<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="date-and-time-types">
      </a>
      13.2 Date and Time Data Types
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="date-and-time-type-syntax.html">
       13.2.1 Date and Time Data Type Syntax
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="datetime.html">
       13.2.2 The DATE, DATETIME, and TIMESTAMP Types
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="time.html">
       13.2.3 The TIME Type
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="year.html">
       13.2.4 The YEAR Type
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="timestamp-initialization.html">
       13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fractional-seconds.html">
       13.2.6 Fractional Seconds in Time Values
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="mysql-calendar.html">
       13.2.7 What Calendar Is Used By MySQL?
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="date-and-time-type-conversion.html">
       13.2.8 Conversion Between Date and Time Types
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="two-digit-years.html">
       13.2.9 2-Digit Years in Dates
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045213881536">
  </a>
  <a class="indexterm" name="idm46045213880080">
  </a>
  <a class="indexterm" name="idm46045213878592">
  </a>
  <p>
   The date and time data types for representing temporal values are
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   , and
   <a class="link" href="year.html" title="13.2.4 The YEAR Type">
    <code class="literal">
     YEAR
    </code>
   </a>
   . Each temporal type has a
      range of valid values, as well as a
   <span class="quote">
    “
    <span class="quote">
     zero
    </span>
    ”
   </span>
   value that
      may be used when you specify an invalid value that MySQL cannot
      represent. The
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   types have special
      automatic updating behavior, described in
   <a class="xref" href="timestamp-initialization.html" title="13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME">
    Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”
   </a>
   .
  </p>
  <p>
   For information about storage requirements of the temporal data
      types, see
   <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
    Section 13.7, “Data Type Storage Requirements”
   </a>
   .
  </p>
  <p>
   For descriptions of functions that operate on temporal values, see
   <a class="xref" href="date-and-time-functions.html" title="14.7 Date and Time Functions">
    Section 14.7, “Date and Time Functions”
   </a>
   .
  </p>
  <p>
   Keep in mind these general considerations when working with date
      and time types:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      MySQL retrieves values for a given date or time type in a
          standard output format, but it attempts to interpret a variety
          of formats for input values that you supply (for example, when
          you specify a value to be assigned to or compared to a date or
          time type). For a description of the permitted formats for
          date and time types, see
      <a class="xref" href="date-and-time-literals.html" title="11.1.3 Date and Time Literals">
       Section 11.1.3, “Date and Time Literals”
      </a>
      . It is expected that
          you supply valid values. Unpredictable results may occur if
          you use values in other formats.
     </p>
    </li>
    <li class="listitem">
     <p>
      Although MySQL tries to interpret values in several formats,
          date parts must always be given in year-month-day order (for
          example,
      <code class="literal">
       '98-09-04'
      </code>
      ), rather than in the
          month-day-year or day-month-year orders commonly used
          elsewhere (for example,
      <code class="literal">
       '09-04-98'
      </code>
      ,
      <code class="literal">
       '04-09-98'
      </code>
      ). To convert strings in other
          orders to year-month-day order, the
      <a class="link" href="date-and-time-functions.html#function_str-to-date">
       <code class="literal">
        STR_TO_DATE()
       </code>
      </a>
      function may be
          useful.
     </p>
    </li>
    <li class="listitem">
     <p>
      Dates containing 2-digit year values are ambiguous because the
          century is unknown. MySQL interprets 2-digit year values using
          these rules:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Year values in the range
         <code class="literal">
          70-99
         </code>
         become
         <code class="literal">
          1970-1999
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Year values in the range
         <code class="literal">
          00-69
         </code>
         become
         <code class="literal">
          2000-2069
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      See also
      <a class="xref" href="two-digit-years.html" title="13.2.9 2-Digit Years in Dates">
       Section 13.2.9, “2-Digit Years in Dates”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Conversion of values from one temporal type to another occurs
          according to the rules in
      <a class="xref" href="date-and-time-type-conversion.html" title="13.2.8 Conversion Between Date and Time Types">
       Section 13.2.8, “Conversion Between Date and Time Types”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL automatically converts a date or time value to a number
          if the value is used in numeric context and vice versa.
     </p>
    </li>
    <li class="listitem">
     <p>
      By default, when MySQL encounters a value for a date or time
          type that is out of range or otherwise invalid for the type,
          it converts the value to the
      <span class="quote">
       “
       <span class="quote">
        zero
       </span>
       ”
      </span>
      value for
          that type. The exception is that out-of-range
      <a class="link" href="time.html" title="13.2.3 The TIME Type">
       <code class="literal">
        TIME
       </code>
      </a>
      values are clipped to the
          appropriate endpoint of the
      <a class="link" href="time.html" title="13.2.3 The TIME Type">
       <code class="literal">
        TIME
       </code>
      </a>
      range.
     </p>
    </li>
    <li class="listitem">
     <p>
      By setting the SQL mode to the appropriate value, you can
          specify more exactly what kind of dates you want MySQL to
          support. (See
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      .) You can get MySQL
          to accept certain dates, such as
      <code class="literal">
       '2009-11-31'
      </code>
      , by enabling the
      <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
       <code class="literal">
        ALLOW_INVALID_DATES
       </code>
      </a>
      SQL
          mode. This is useful when you want to store a
      <span class="quote">
       “
       <span class="quote">
        possibly
          wrong
       </span>
       ”
      </span>
      value which the user has specified (for example,
          in a web form) in the database for future processing. Under
          this mode, MySQL verifies only that the month is in the range
          from 1 to 12 and that the day is in the range from 1 to 31.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL permits you to store dates where the day or month and
          day are zero in a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
      or
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      column. This is useful
          for applications that need to store birthdates for which you
          may not know the exact date. In this case, you simply store
          the date as
      <code class="literal">
       '2009-00-00'
      </code>
      or
      <code class="literal">
       '2009-01-00'
      </code>
      . However, with dates such as
          these, you should not expect to get correct results for
          functions such as
      <a class="link" href="date-and-time-functions.html#function_date-sub">
       <code class="literal">
        DATE_SUB()
       </code>
      </a>
      or
      <a class="link" href="date-and-time-functions.html#function_date-add">
       <code class="literal">
        DATE_ADD()
       </code>
      </a>
      that require
          complete dates. To disallow zero month or day parts in dates,
          enable the
      <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
       <code class="literal">
        NO_ZERO_IN_DATE
       </code>
      </a>
      mode.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL permits you to store a
      <span class="quote">
       “
       <span class="quote">
        zero
       </span>
       ”
      </span>
      value of
      <code class="literal">
       '0000-00-00'
      </code>
      as a
      <span class="quote">
       “
       <span class="quote">
        dummy
          date.
       </span>
       ”
      </span>
      In some cases, this is more convenient than
          using
      <code class="literal">
       NULL
      </code>
      values, and uses less data and
          index space. To disallow
      <code class="literal">
       '0000-00-00'
      </code>
      ,
          enable the
      <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
       <code class="literal">
        NO_ZERO_DATE
       </code>
      </a>
      mode.
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="quote">
       “
       <span class="quote">
        Zero
       </span>
       ”
      </span>
      date or time values used through
          Connector/ODBC are converted automatically to
      <code class="literal">
       NULL
      </code>
      because ODBC cannot handle such
          values.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following table shows the format of the
   <span class="quote">
    “
    <span class="quote">
     zero
    </span>
    ”
   </span>
   value for each type. The
   <span class="quote">
    “
    <span class="quote">
     zero
    </span>
    ”
   </span>
   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
   <code class="literal">
    '0'
   </code>
   or
   <code class="literal">
    0
   </code>
   , which are easier
      to write. For temporal types that include a date part
      (
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   , and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   ), use of these values may
      produce warning or errors. The precise behavior depends on which,
      if any, of the strict and
   <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
    <code class="literal">
     NO_ZERO_DATE
    </code>
   </a>
   SQL modes are
      enabled; see
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <div class="informaltable">
   <table summary="Format of the zero value for temporal data types.">
    <colgroup>
     <col style="width: 30%"/>
     <col style="width: 40%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Data Type
      </th>
      <th>
       <span class="quote">
        “
        <span class="quote">
         Zero
        </span>
        ”
       </span>
       Value
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         DATE
        </code>
       </a>
      </td>
      <td>
       <code class="literal">
        '0000-00-00'
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <a class="link" href="time.html" title="13.2.3 The TIME Type">
        <code class="literal">
         TIME
        </code>
       </a>
      </td>
      <td>
       <code class="literal">
        '00:00:00'
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         DATETIME
        </code>
       </a>
      </td>
      <td>
       <code class="literal">
        '0000-00-00 00:00:00'
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         TIMESTAMP
        </code>
       </a>
      </td>
      <td>
       <code class="literal">
        '0000-00-00 00:00:00'
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <a class="link" href="year.html" title="13.2.4 The YEAR Type">
        <code class="literal">
         YEAR
        </code>
       </a>
      </td>
      <td>
       <code class="literal">
        0000
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
 </div>
 <br/>
</div>
