Copyright (C) 2000-2012 |
GNU Info (mysql.info)Using DATEProblems Using `DATE' Columns ----------------------------- The format of a `DATE' value is `'YYYY-MM-DD''. According to ANSI 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 no checking whether or not the date is correct. If you store an incorrect date, such as `'1998-2-31'', the wrong date will be stored. If the date cannot be converted to any reasonable value, a `0' is stored in the `DATE' field. This is mainly a speed issue and we think it is up to the application to check the dates, and not the server. automatically generated by info2www version 1.2.2.9 |