DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) using-date

Info Catalog (mysql.info) case-sensitivity (mysql.info) query-issues (mysql.info) 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.
 
 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.
 
Info Catalog (mysql.info) case-sensitivity (mysql.info) query-issues (mysql.info) problems-with-null
automatically generated byinfo2html