InterBase (28)
MS-SQL (5)
mysql (36)
Oracle (1)
Exchange Links
About this site
Handling of dates in mysql (Date formats)
This article has not been rated yet. After reading, feel free to leave comments and rate it.
Question:
How do I express a date in a SQL statement for the mysql database? I use version 3.23.Answer:
The format of data type DATE is 'YYYY-MM-DD'. ANSI SQL says that nothing else is allowed.As a convinience MySQL automaticly converts the date to a number if used in a number context. It's also smart enough to allow a 'relaxed' string form when updating, but only when updating.
Below you find a few examples what works and what does not work.
 |  | |
/* these all work in mysql */ /* 1997/05/05 */ insert into table_1 (idate) values (19970505) ; insert into table_1 (idate) values ('19970505') ; insert into table_1 (idate) values ('1997-05-05'); insert into table_1 (idate) values ('1997.05.05'); insert into table_1 (idate) values ('1997 05 05'); select idate from table_1 where idate >= '1997-05-05'; select idate from table_1 where idate >= 19970505; select mod(idate,100)1 from table_1 where idate >= 19970505; /* The following will not work */ /* because '19970505' is compared as a string to '1997-05-05' */ select idate from table_1 where idate >= '19970505'; | ||
 |  |
Comments:
| ||
|
Maybe it would be useful to use another date, like the 27th of May
instead of 05/05 to make sure no one mixes up day and month of your example. |
