DelphiFAQ Home Search:

Handling of dates in mysql (Date formats)

 

commentsThis 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:

2008-04-08, 06:06:33
anonymous from Germany  
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.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: