Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

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 permitted. You should use this format in UPDATE expressions and in the WHERE clause of SELECT statements. For example:

SELECT * FROM t1 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. MySQL also permits a relaxed string format when updating and in a WHERE clause that compares a date to a DATE, DATETIME, or TIMESTAMP column. Relaxed format 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 comparison method other than those just listed, such as IN or STRCMP().

For those exceptions, the comparison is done by converting the objects to strings and performing a string comparison.

To be on the safe side, 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 zero date '0000-00-00' can be stored and retrieved as '0000-00-00'. When a '0000-00-00' date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date.

Because MySQL performs the conversions just described, the following statements work (assume that idate is a DATE column):

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

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

However, the following statement does not work:

SELECT idate FROM t1 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 enable the ALLOW_INVALID_DATES SQL mode, MySQL permits 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).

MySQL permits you to store dates where the day or month and day are zero. This is convenient if you want to store a birthdate in a DATE column and you know only part of the date. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE mode.

MySQL permits you to store a zero value of '0000-00-00' as a dummy date. This is in some cases more convenient than using NULL values. If a date to be stored in a DATE column cannot be converted to any reasonable value, MySQL stores '0000-00-00'. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

To have MySQL check all dates and accept only legal dates (unless overridden by IGNORE), set the sql_mode system variable to "NO_ZERO_IN_DATE,NO_ZERO_DATE".

Download this Manual
User Comments
  Posted by Jörg Höhle on January 9, 2003
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
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 Nick Springer on April 26, 2003
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)
dd = day(varDate)
end if

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

convertDate = year(varDate) & mm & dd
end function
  Posted by Jorge Solis on April 26, 2003
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
  Posted by mark roth on June 22, 2004
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
Here is an ASP function for changing DATE and TIME for mysql odbc:

function mysqldate(dt)
'dt should be a date time string
if isdate(dt) then
mysqldate = year(dt) & "-" & month(dt) & "-" & day(dt) & " " & hour(dt) & ":" & minute(dt) & ":" & second(dt)
mysqldate = 0
end if
end function
  Posted by Jonathan Woods on November 9, 2005
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:


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 '&amp;'.

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

  Posted by Komang Arthayasa on October 20, 2006
Here are some usefull Date/Time Conversion in ASP

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

Function FormatMySQLDate(sdate,booltime)
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

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

  Posted by Marcus Matos on January 8, 2008
Important: It should be known that MySQL >= 5.0.42 silently changes the behavior of comparing a DATE column to NOW().


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.

Sign Up Login You must be logged in to post a comment.