(mysql.info.gz) Using DATE

Info Catalog

(mysql.info.gz) Case sensitivity

(mysql.info.gz) Query Issues

(mysql.info.gz) Problems with NULL

 
 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 >= '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.
 
 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 will 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 ensures 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 will be 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 overriden 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.
 

Info Catalog

(mysql.info.gz) Case sensitivity

(mysql.info.gz) Query Issues

(mysql.info.gz) Problems with NULL