Jump to content

filter date to show dates after a set date


jonnyfortis

Recommended Posts

i have a table that shows payments made but  want to the payments only showing from a set date(06/12/14) and before this date i dont want to show

 

this is my sql that doesnt seem to work and is showing dates before the specified date.

.

"SELECT * FROM payments2014, signup2014, editprop2014 WHERE signup2014.userid = payments2014.payment_userid AND editprop2014.prop_id = signup2014.prop_id AND signup2014.userid !='page1' AND signup2014.userid !='page6' AND signup2014.userid !='page4' AND payments2014.payment_transaction_status !='none' AND payments2014.payment_transaction_status !='CANCELLEDa' AND payments2014.payment_type !='deposit' AND payments2014.payment_paid_timestamp NOT LIKE '%2012%' AND payments2014.payment_paid_timestamp NOT LIKE '%2011%' AND payments2014.payment_paid_timestamp >= '06/12/14' ORDER BY payments2014.payment_id DESC"

i have some other parts in the statment but this one that should be filtering is 

host_payments2014.payment_paid_timestamp >= '06/12/14' 

thanks in advance

Link to comment
Share on other sites

How is the date stored in the "host_payments2014.payment_paid_timestamp" field? It sounds like it's stored as a plain string instead of a date. More information about dates in MySQL can be found here:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

yes it currently is stored as VARCHAR, i need to change it to DATE but this issue i am having is the return date from the bank is MM/DD/YY and the date format is 0000-00-00 in  phpMyAdmin and i not sure if the return date will populate the column correctly.

Link to comment
Share on other sites

For now you can use

WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12'

But you should change your table to use the correct type and format. You can use that same function to convert your dates.

 

Also, don't use the "FROM X, Y, Z WHERE... " syntax. You should use explicit JOIN syntax ie

FROM X 
INNER JOIN Y ON ... 
INNER JOIN Z ON ...
  • Like 1
Link to comment
Share on other sites

 

For now you can use

WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12'

But you should change your table to use the correct type and format. You can use that same function to convert your dates.

 

Also, don't use the "FROM X, Y, Z WHERE... " syntax. You should use explicit JOIN syntax ie

FROM X 
INNER JOIN Y ON ... 
INNER JOIN Z ON ...

i am trying 

 

WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12'

 

but am getting an error from another part of the page.

 

Warning: Division by zero in

 

the line of code is.

 

$additional1 = $row_rsPayment['payment_amount_paid'] / $row_rsPayment['rental_price']; // divide this by property week amount ; 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.