MySQL datetime(ignoring the time element in a query via PHP)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

MySQL datetime(ignoring the time element in a query via PHP)

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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";
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post 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";
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

I don't really understand it but it works, i'll study it some more, thank you :)

Rob
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

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