The format of DATE is 'YYYY-MM-DD'. According to ANSI SQL nothing else is
allowed. One should use this format to update or in the WHERE clause,
ie select * from table_1 where date >= '1997-05-05';
As a convenience, MySQL automatically converts the date to a
number if used in a number context. It is also smart enough to allow a
'relaxed' string form when updating and in a WHERE with a compare
to a TIMESTAMP, DATE or a DATETIME column.
The special date '0000-00-00' can be stored and retrieved as 0000-00-00. When using a '0000-00-00' date trough MyODBC it will automaticly be converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of dates.
This means that the following works:
insert into table_1 (idate) values (19970505) ;
insert into table_1 (idate) values ('19970505') ;
insert into table_1 (idate) values ('97-05-05');
insert into table_1 (idate) values ('1997.05.05');
insert into table_1 (idate) values ('1997 05 05');
insert into table_1 (idate) values ('0000-00-00');
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;
select idate from table_1 where idate >= '19970505';
The following will not work:
select idate from table_1 where strcmp(idate,'19970505')=0; Because '19970505' is compared as a string to '1997-05-05'.
Note that MySQL does no checking if the date is correct. If you store a wrong date, like 1998-2-31, the wrong date will be stored. If the date is totally impossible a 0 is stored in the date field.. This is mainly a speed issue and we think it's up to the application to check the dates and not the server.
Go to the first, previous, next, last section, table of contents.