6.2.2 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 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 following table 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.

Subsections

• 6.2.2.1 Y2K Issues and Date Types
• 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types
• 6.2.2.3 The TIME Type
• 6.2.2.4 The YEAR Type

User Comments

Add your own comment.