Page 1 of 1
MySQL datetime(ignoring the time element in a query via PHP)
Posted: Tue Nov 08, 2005 10:04 am
by robster
Hi all,
I have this SQL I wish to perform:
Code: Select all
$sql = "SELECT * FROM pos_history WHERE date = '$short_date' ORDER BY id ASC";
The problem is, $short_date is something like
2005-12-28 and the date field in the database is something like
2005-12-28 09:36:42
Obviously they don't match, but the question is, how can I tell it to only match the YEAR-MONTH-DAY part of the entry in the database and ignore the time element?
A toughy! (for me

)
All suggestions appreciated.
Rob
Posted: Tue Nov 08, 2005 10:22 am
by feyd
Posted: Tue Nov 08, 2005 4:52 pm
by robster
Thanks for that, the feature looks really useful but I'm having a lot of trouble with it.
I've tried this (as well as many variants) but am having no luck, this one it seems returns ALL the records in the table, not just the ones on the date in $short_date.
Any advice appreciated.
Rob
Code: Select all
$sql = "SELECT * FROM pos_history WHERE DATE_ADD('$short_date', INTERVAL 1 DAY) ORDER BY id ASC";
Posted: Tue Nov 08, 2005 5:02 pm
by wtf
try this
Code: Select all
mysql> select date from cart where DATE(date) = '2005-10-17';
+---------------------+
| date |
+---------------------+
| 2005-10-17 12:10:59 |
| 2005-10-17 12:10:16 |
| 2005-10-17 01:10:50 |
| 2005-10-17 01:10:57 |
| 2005-10-17 01:10:50 |
| 2005-10-17 01:10:36 |
| 2005-10-17 01:10:55 |
| 2005-10-17 01:10:04 |
| 2005-10-17 01:10:42 |
| 2005-10-17 08:10:53 |
| 2005-10-17 08:10:51 |
+---------------------+
in your case it would be something like this
Code: Select all
$sql = "SELECT * FROM pos_history WHERE DATE(date) = '$short_date' ORDER BY id ASC";
Posted: Tue Nov 08, 2005 5:34 pm
by robster
I don't really understand it but it works, i'll study it some more, thank you
Rob
Posted: Wed Nov 09, 2005 10:01 am
by wtf
NP!
If you follow the link feyd posted, you'll see bunch of examples on how to use date functions in mysql. I tried using DATE_FORMAT() as he suggested but could not get it to work.