DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Handling of dates in mysql (Date formats)

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';
 

Generated 0:00:38 on Dec 15, 2017