A.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 >= '1997-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 a DATETIME column. (Relaxed form means that any punctuation character may be used as the separator between parts. For example, '1998-08-15' and '1998#08#15' are equivalent.) MySQL can also convert a string containing no separators (such as '19980815'), provided it makes sense as a date.

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 will automatically be converted to NULL in MyODBC Version 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 will not work:

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

STRCMP() is a string function, so it converts idate to a string and performs a string comparison. It does not convert '19970505' to a date and perform a date comparison.

Note that MySQL does very limited checking whether the date is correct. If you store an incorrect date, such as '1998-2-31', the wrong date will be stored.

Because MySQL packs dates for storage, it can't store any given date as it would not fit onto the result buffer. The rules for accepting a date are:

• If MySQL can store and retrieve a given date, the wrong date is accepted for DATE and DATETIME columns.
• All days values between 0-31 are accepted for any date. This makes it very convenient for web applications where you ask year, month and day in 3 different fields.
• The day or month field may be zero. This is convenient if you want to store a birthdate in a DATE column and you only know part of the date.

If the date cannot be converted to any reasonable value, a 0 is stored in the DATE field, which will be retrieved as 0000-00-00. This is both a speed and convenience issue as we believe that the database'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 to check the dates, and not the server.

User Comments

Unless I find a way to display the date for update or
insert in the american format of month-day-year the
company will not use MySQL. Using APS and
MyODBC shows date in the correct format but data
is all screwed up when saving it (it converts to year-
month-day). I'd rather use PHP with my
Dreamweaver but there the dates can not be
formatted at all for update. Help! :)

On a textarea the visitor inserts the date in
'dd/mm/yyyy' format.

With a php script i change the format to
'yyyy-mm-dd' (it seems mysql format-like), but in
the data base the date is incorrect (0000-00-00).

Julian...make sure to INSERT your date as a string.
E.g. INSERT INTO table (date) VALUES ('YYYY-MM-
DD')
not INSERT INTO table (date) VALUES (YYYY-MM-DD)

Hello All
the solution for ms database with mmddYYYY dates
is to insert the data as varchar or char, run the script
below and change the field(s) to date after that
conversion.

Worked wonders for us trying to convert cvs data
from excel to mysql.




<?
mysql_pconnect("host","user","pass") or die
("Unable to
connect to SQL server"); mysql_select_db
("DBname") or die("Unable to select
database");

function truena_en_pedacitos($string)
{
if($string != "")
{
$cnt=0;
$token=strtok(trim($string), "/");
while($token)
{
$cnt++;
$var[$cnt]=$token;
$token=strtok("/");
}

$date = date("Y-m-d", mktime(0,0,0,$var[1],
$var[2], $var[3])); }
else {
$date = "0000-00-00";
}

return $date;
}


$result = mysql_query("SELECT id, res_date,
arr_date, dep_date FROM
reservationsold"); $rown = mysql_num_rows
($result);

echo "Cambiando fechas chafas a fechas chidas ...";
echo "";

$counter = 0;
while($counter < $rown)
{
$the_id = mysql_result($result, $counter, "id");
$reservation = truena_en_pedacitos
(mysql_result($result, $counter,
"res_date")); $arrival = truena_en_pedacitos
(mysql_result($result,
$counter, "arr_date")); $departure =
truena_en_pedacitos(mysql_result($result,
$counter, "dep_date"));

echo "update reservationsold set res_date
= '$reservation',
arr_date = '$arrival', dep_date = '$departur
e' where id = '$the_id'";
echo "";

$insert = mysql_query("update reservationsold
set res_date =
'$reservation', arr_date = '$arrival', dep_
date = '$departure' where id = '$the_id'");

$counter++;
}

?>

Note to Lance: Thanks SO much for this piece of code that
changes the date format. Tons of people will want to use
this (or a variant thereof).

I have done a similar thing using SQL code so file upload
and format changes can be done with a single command.
Those who need this kind of thing can use my code as a
starting point. You'll find it at http://www.albooth.com/
mysqldateformatchanger.html.

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

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)!

How do you select between and dates: I tried the comparison operator between and as well as date >= 'yyyy-mm-dd' AND date <='yyyy-mm-dd' and they both returned empty sets. However when I select ---> date >='yyyy-mm-dd' I get a result set...

any suggestions?

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;

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
%>

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

Hi Everyone,

We need to switch to MySql from MS Access. I am not able to update the date column in my recordset. I use the following :
With mDb
.CursorLocation = adUseClient
.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};SERVER=;DATABASE = XXX;UID=;PWD=;option =2"
.Mode = adModeReadWrite
.Open "XXX"
End With

Dim mRS As Recordset
Set mRS = New ADODB.Recordset
mRS.CursorLocation = adUseClient

With mRS
.Source = "SELECT * From Student where StudentNo = 50"
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = mDb
End With

mRS.Open
mRS.MoveFirst
mRS("BirthDate").Value = Date
mRS.Update

It gives me this error "Row cannot be loacated for updating. Some values may have been changed since it was last read."

Has anyone come across anything like this. I am able to update other columns of type (String / VarChar / Number), But updating a date gives me this error. Please let me know if someone has come accross this situation. It will be really appreciated.

Add your own comment.