Date Format

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Date Format

Post 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?
qartis
Forum Contributor
Posts: 271
Joined: Sat Dec 14, 2002 4:43 pm
Location: BC, Canada
Contact:

Post by qartis »

SELECT `feilds` FROM `table` WHERE SUBSTRING_INDEX(`f_date_closed`, ' ', 1) = '$today_search'
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

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