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 ...
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

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.