Page 1 of 1

Date Format

Posted: Wed Jul 23, 2003 10:41 am
by gurjit
I have a datetime field type in a table which stores the date like this

2003-07-23 16:38

i'm running a query to search a date range but i cannot get the query to just look at the date part in the WHERE statement

WHERE f_date_closed = '$today_search'

the f_date_closed has date stored like 2003-07-23 16:38 but i am searching for 2003-07-23 in the today_search parameter.

How do you only pull the date in the f_date_closed field and ignore the time?

Posted: Wed Jul 23, 2003 1:37 pm
by qartis
SELECT `feilds` FROM `table` WHERE SUBSTRING_INDEX(`f_date_closed`, ' ', 1) = '$today_search'

...

Posted: Wed Jul 23, 2003 1:42 pm
by kettle_drum
You can either store the date as the UNIX epoch (i.e. number of seconds passed since january 1970) this then means you can use an sql statment like:

SELECT * FROM blah WHERE date BETWEEN '$start_of_day' AND '$end_of_day'

Or if you still wish to store the date as you ahve it you should be able to do:

SELECT * FROM blah WHERE date LIKE '$today%'

where the % is a wildcard.