7.3.6 Date and time types
The date and time types are DATETIME, DATE, TIMESTAMP, TIME and YEAR. Each of these has a range of legal values, as well as a ``zero'' value that is used when you specify a really illegal value. Note that MySQL allows you to store certain 'not strictly' legal date values, for example 1999-11-31. The reason for this is that we think it's the responsibility of the application to handle date checking, not the SQL servers. To make the date checking 'fast', MySQL only checks that the month is in the range of 0-12 and the day is in the range of 0-31. The above ranges are defined this way because MySQL allows you to store, in a DATE or DATETIME column, dates where the day or month-day are zero. This is extremely useful for applications that need to store a birth-date for which you don't know the exact date. In this case you simply store the date like 1999-00-00 or 1999-01-00. (You can of course not expect to get a correct value from functions like DATE_SUB() or DATE_ADD for dates like these).
Here are some general considerations to keep in mind when working with date and time types:
- MySQL retrieves values for a given date or time type in a standard format, but it attempts to interpret a variety of formats for values that you supply (e.g., when you specify a value to be assigned to or compared to a date or time type). Nevertheless, only the formats described in the following sections are supported. It is expected that you will supply legal values, and unpredictable results may occur if you use values in other formats.
- Although MySQL tries to interpret values in several formats, it always expects the year part of date values to be leftmost. Dates must be given in year-month-day order (e.g.,
'98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (e.g., '09-04-98', '04-09-98'). - MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa.
- When MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (see start of this section), it converts the value to the ``zero'' value for that type. (The exception is that out-of-range
TIME values are clipped to the appropriate endpoint of the TIME range.) The table below shows the format of the ``zero'' value for each type: | Column type | ``Zero'' value |
DATETIME | '0000-00-00 00:00:00' |
DATE | '0000-00-00' |
TIMESTAMP | 00000000000000 (length depends on display size) |
TIME | '00:00:00' |
YEAR | 0000 |
- The ``zero'' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values
'0' or 0, which are easier to write. - ``Zero'' date or time values used through MyODBC are converted automatically to
NULL in MyODBC 2.50.12 and above, because ODBC can't handle such values.
Comments
