Skip navigation links
B.5.5.3 Problems with NULL Values »
Section Navigation      [Toggle]

B.5.5.2. Problems Using DATE Columns

The format of a DATE value is 'YYYY-MM-DD'. According to standard SQL, no other format is allowed. You should use this format in UPDATE expressions and in the WHERE clause of SELECT statements. For example: 

mysql> SELECT * FROM tbl_name WHERE date >= '2003-05-05';

As a convenience, MySQL automatically converts a date to a number if the date is used in a numeric context (and vice versa). It is also smart enough to allow a “relaxed” string form when updating and in a WHERE clause that compares a date to a TIMESTAMP, DATE, or DATETIME column. (“Relaxed form” means that any punctuation character may be used as the separator between parts. For example, '2004-08-15' and '2004#08#15' are equivalent.) MySQL can also convert a string containing no separators (such as '20040815'), provided it makes sense as a date.

When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the <, <=, =, >=, >, or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparison (and also for a bit more “relaxed” string checking). However, this conversion is subject to the following exceptions:

  • When you compare two columns

  • When you compare a DATE, TIME, DATETIME, or TIMESTAMP column to an expression

  • When you use any other comparison method than those just listed, such as IN or STRCMP().

For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison.

To keep things safe, assume that strings are compared as strings and use the appropriate string functions if you want to compare a temporal value to a string.

The special date '0000-00-00' can be stored and retrieved as '0000-00-00'. When using a '0000-00-00' date through MyODBC, it is automatically converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of date.

Because MySQL performs the conversions described above, the following statements work: 

mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

However, the following does not work: 

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'20030505')=0;

STRCMP() is a string function, so it converts idate to a string in 'YYYY-MM-DD' format and performs a string comparison. It does not convert '20030505' to the date '2003-05-05' and perform a date comparison.

If you are using the ALLOW_INVALID_DATES SQL mode, MySQL allows you to store dates that are given only limited checking: MySQL requires only that the day is in the range from 1 to 31 and the month is in the range from 1 to 12.

This makes MySQL very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation).

If you are not using the NO_ZERO_IN_DATE SQL mode, the day or month part can be zero. This is convenient if you want to store a birthdate in a DATE column and you know only part of the date.

If you are not using the NO_ZERO_DATE SQL mode, MySQL also allows you to store '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values.

If the date cannot be converted to any reasonable value, a 0 is stored in the DATE column, which is retrieved as '0000-00-00'. This is both a speed and a convenience issue. We believe that the database server's responsibility is to retrieve the same date you stored (even if the data was not logically correct in all cases). We think it is up to the application and not the server to check the dates.

If you want MySQL to check all dates and accept only legal dates (unless overridden by IGNORE), you should set sql_mode to "NO_ZERO_IN_DATE,NO_ZERO_DATE".

Date handling in MySQL 5.0.1 and earlier works like MySQL 5.0.2 with the ALLOW_INVALID_DATES SQL mode enabled.


User Comments

Posted by Jörg Höhle on January 9 2003 6:27am[Delete] [Edit]

It seems that the 0000-00-00 to NULL conversion also affects JDBC (mysql connectorJ 3.0.3-beta), not only ODBC.

Posted by Rory Sellers on January 27 2003 1:25pm[Delete] [Edit]

I'm surprised nobody has mentioned DATE_FORMAT(). After all, the problem for many folks isn't how the data is stored, but how it is displayed, and reformatting the display through the database at SELECT-time is almost always easier than using the application (e.g. PHP or whatever)!

Posted by Amie Wiseley on April 17 2003 5:01am[Delete] [Edit]

Just tell MySQL to format the date when you do your select statement and then it will show up nicely formatted (using DATE_FORMAT) ...

EXAMPLE
select DATE_FORMAT(yourcolumnname, '%M %e %Y') from yourtablename;

Posted by Nick Springer on April 26 2003 6:32am[Delete] [Edit]

Here is a simple ASP/VBScript function you can use to convert a VBScipt date variable in a MySQL compatible string. Place the function in an APS doc and anytime you need a MySQL date use "convertDate(your_date)"

<%
function convertDate(varDate)
if day(varDate) < 10 then
dd = "0" & day(varDate)
else
dd = day(varDate)
end if

if month(varDate) < 10 then
mm = "0" & month(varDate)
else
mm = month(varDate)
end if

convertDate = year(varDate) & mm & dd
end function
%>

Posted by Jorge Solis on April 26 2003 9:25am[Delete] [Edit]

Select a Date between dates

mySQL supports the between operator. SELECT * FROM Appointments WHERE
Moment BETWEEN StartTime AND EndTime ORDER BY StartTime;

Anyway, you need to use 20030427, not 2003-04-27

Jorge Solis
flash-db.com

Posted by Steve Geer on April 9 2004 1:10pm[Delete] [Edit]

In addition to what Amie said...

This is the method I have used and it works well:

SELECT DATE_FORMAT( `row_for_date` , '%c-%e-%y' ) AS revised_date, TIME_FORMAT( `row_for_time` , '%h:%i %p' ) AS revised_time FROM table_name

Then throw it into a while loop like so:

while($row = mysql_fetch_assoc($query_above)) {
echo 'The date is : '.$row['revised_date'].'<br>';
echo 'The time is : '.$row['revised_time'];
}

This, of course, needs to have the values of the DATE and TIME in the database already. In addition, you can add a WHERE to the end of the query to limit your search.

-Steveo

Posted by mark roth on June 22 2004 1:43pm[Delete] [Edit]

After much hair-tearing, I finally found how to do timestamp *comparisons*: the number which the timestamp column is being compared to *must* be valid numeric time. 20040622585858 is fine, but 20040622685800 (note the 68th minute) results in what appears to be a non-comparison, and everything is selected.

I would have expected a straight numeric comparision, but nooooo...

Posted by Michael Last on June 30 2005 9:36pm[Delete] [Edit]

Here is an ASP function for changing DATE and TIME for mysql odbc:

function mysqldate(dt)
'dt should be a date time string -michaeldarkblue@yahoo.com
if isdate(dt) then
mysqldate = year(dt) & "-" & month(dt) & "-" & day(dt) & " " & hour(dt) & ":" & minute(dt) & ":" & second(dt)
else
mysqldate = 0
end if
end function

Posted by Jonathan Woods on November 9 2005 3:27pm[Delete] [Edit]

For anyone using JDBC and experiencing problems with NULL date columns (e.g. J�rg H�hle above): you can ask MySQL nicely to return NULLs for date columns holding 0000-00-00 - the sensible behaviour, imho. To do this, set the property zeroDateTimeBehaviour when creating the JDBC Connection. It seems it is best to do this by specifying the property as part of a JDBC URL, so:

jdbc:mysql://{host}:{port}/{dbname}?user={user}&password={password}&zeroDateTimeBehavior=convertToNull

where you should use the appropriate values for the stuff in curly braces.

NOTE: if you're specifying this URL in an XML file, e.g. for the web.xml context descriptor of a servlet-based web app, replace '&' with '&'.

For more details and a better explanation, Google for zeroDateTimeBehavior.

Jon

Posted by Komang Arthayasa on October 20 2006 2:37am[Delete] [Edit]

Here are some usefull Date/Time Conversion in ASP

'***** function to COnvert date to mySQL accepted format

Function FormatMySQLDate(sdate,booltime)
FormatMySQLDate=year(sdate)&"-"&month(sdate)&"-"&day(sdate)
if booltime then
FormatMySQLDate=FormatMySQLDate&" "&formatDatetime(sdate,4)
end if
End Function

'***** toConvert Date To NUmeric or the opposite
'***** for comparing date

Function DateToNum(sdate)
DateToNum=year(sdate) & right("00"&month(sdate),2) & right("00"&day(sdate),2)
End Function

Function NumToDate(sdate)
NumToDate=left(sdate,4) & "/" & mid(sdate,5,2) & "/" & right(sdate,2)
End Function

NOTE:
sDate Must Be a date/time variable value, booltime mean is the time format included

Posted by Amit Kondhare on November 9 2006 7:52am[Delete] [Edit]

No ready made function is provided for validate date

This function work
(you can take what ever size you want in varchar(1-1024) )

CREATE FUNCTION IsDate (sIn varchar(1024)) RETURNS INT
BEGIN
declare tp int;

if length(date(sIn)) is not null then
set tp = 0;
else
set tp = 1;
end if;
RETURN tp;
END
If you find any bug for this please post it here as this is not complete soluction as date respond are not known
I will try to solve this

Posted by Marcus Matos on January 8 2008 1:02pm[Delete] [Edit]

Important: It should be known that MySQL >= 5.0.42 silently changes the behavior of comparing a DATE column to NOW().

See: http://bugs.mysql.com/bug.php?id=28929

This breaks many things since now queries using WHERE datecol = NOW() will return NULL where previously it would return results.

Use CURDATE() instead. I'm having to go back through years of code to fix this.


Posted by Enrico Di Piero on November 13 2009 11:30am[Delete] [Edit]

I use this function on testing if a value is a correct date;

CREATE FUNCTION isdate(EVAL_DATE VARCHAR(1024))
RETURNS tinyint(1)
BEGIN
RETURN NOT(ISNULL(DATEDIFF(CURRENT_DATE,EVAL_DATE)));
END;

the difference between a date and a not-date value is always a null value, so you have to test only for a null result and, as all the is... functions, return 1 if the tested value is a date and 0 in the other case.

Posted by Jack Karapatakis on February 16 2010 8:17pm[Delete] [Edit]

the last implementation of isDate yields (1) for the obviously invalid date.
SELECT isDate('2010-02-28 eqw');

jack

Add your own comment.