Copyright (C) 2000-2012 |
GNU Info (mysql.info)Date and time typesDate 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 is 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 cannot 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 (for example, 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 (for example, `'98-09-04''), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, `'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 the 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* Version 2.50.12 and above, because ODBC can't handle such values. automatically generated by info2www version 1.2.2.9 |