A.5.2 Проблемы с использованием столбцов типа DATE

Значения типа DATE имеют формат YYYY-MM-DD; согласно стандарту ANSI SQL, никакой другой формат не допускается. Пользователь должен применять этот формат в выражениях UPDATE и в определении WHERE операторов SELECT. Например:

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

Из соображений удобства MySQL автоматически преобразовывает дату в число, если дата используется в числовом контексте (и наоборот). Благодаря своей ``разумности'' MySQL допускает также ``мягкую'' строковую форму при обновлении и в определении WHERE, сравнивающем дату со столбцом типа TIMESTAMP, DATE или DATETIME ("мягкая" форма означает, что для разделения составляющих даты можно использовать любой знак пунктуации; например, 1998-08-15 и 1998#08#15 эквивалентны). MySQL может также преобразовывать в даты строки, не содержащие разделителей (наподобие 19980815), при условии, что представляемая строкой дата не лишена смысла.

Специальная дата 0000-00-00 может записываться и извлекаться в виде 0000-00-00. При использовании даты 0000-00-00 из MyODBC она будет автоматически преобразована в NULL в версии MyODBC 2.50.12 и выше, так как ODBC не обеспечивает обработку такого рода дат.

Поскольку в MySQL выполняются описанные выше преобразования, следующие команды будут работать:

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

Однако приведенные ниже команды работать не будут:

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

STRCMP() - строковая функция, следовательно, она преобразует idate в строку и сравнивает строки. Функция не станет преобразовывать 19970505 в дату и сравнивать даты.

Заметим, что MySQL не проверяет правильности дат, поэтому такая некорректная дата, как 1998-2-31, будет занесена в базу. Если же дату нельзя преобразовать в какое-нибудь разумное значение, в поле типа DATE заносится 0. Проверка правильности дат не делается - в основном из соображений, связанных со скоростью: мы считаем, что проверкой дат должно заниматься приложение, а не сервер.

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

Add your own comment.