I have a bunch of entries in a database, which I want to sort via date, but the date format looks like this Y-m-d g:i:s, how could I search the database via the date, and not date time?
views:
78answers:
5
A:
That's easy. Convert your dates into proper format first, which is Y-m-d h:i:s
Col. Shrapnel
2010-09-01 12:16:17
It shows up in the database as Y-m-d h:i:s, but I am unsure on how I can go about querying the entries based on the date, and not the date time, like so: "SELECT * WHERE dateTime='2010-05-08'" for instance, is that a possibility?
2010-09-01 12:21:50
@kielie if it's already in the proper format, use date() function for filtering: `WHERE date(dateTime)='2010-05-08'"` and there should be no problem with ordering already
Col. Shrapnel
2010-09-01 12:26:03
A:
You can do:
ORDER BY UNIX_TIMESTAMP(your_date)
your_date date needs to be converted first to Y-m-d h:i:s
fatnjazzy
2010-09-01 12:17:36
poster does not mention timestamp usage in the table. Date is already Y-m-d h:i:s
Jakub
2010-09-01 12:25:27
if it gets converted to Y-m-d h:i:s, no need to use timestamp for ordering. Y-m-d h:i:s will be ordered all right. That's the purpose of such a format
Col. Shrapnel
2010-09-01 12:27:18
A:
here is an example
$sql="SELECT cast(".$date." AS datetime) as sortdate FROM table order by sortdate ASC";
or
$sql="SELECT cast(DateColumnName AS datetime) as sortdate FROM table order by sortdate ASC";
JapanPro
2010-09-01 12:22:19