<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="using-date">
      </a>
      B.3.4.2 Problems Using DATE Columns
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053677248">
  </a>
  <a class="indexterm" name="idm46045053676208">
  </a>
  <a class="indexterm" name="idm46045053674720">
  </a>
  <p>
   The format of a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   value is
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
     </code>
    </em>
    '
   </code>
   .
          According to standard SQL, no other format is permitted. You
          should use this format in
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   expressions and in the
   <code class="literal">
    WHERE
   </code>
   clause of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa50930392"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token datatype">date</span> <span class="token operator">&gt;=</span> <span class="token string">'2003-05-05'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As a convenience, MySQL automatically converts a date to a
          number if the date is used in numeric context and vice versa.
          MySQL also permits a
   <span class="quote">
    “
    <span class="quote">
     relaxed
    </span>
    ”
   </span>
   string format when
          updating and in a
   <code class="literal">
    WHERE
   </code>
   clause that
          compares a date to a
   <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>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column.
   <span class="quote">
    “
    <span class="quote">
     Relaxed
    </span>
    ”
   </span>
   format means that any punctuation
          character may be used as the separator between parts. For
          example,
   <code class="literal">
    '2004-08-15'
   </code>
   and
   <code class="literal">
    '2004#08#15'
   </code>
   are equivalent. MySQL can also
          convert a string containing no separators (such as
   <code class="literal">
    '20040815'
   </code>
   ), provided it makes sense as a
          date.
  </p>
  <p>
   When you compare a
   <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>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   to a constant string
          with the
   <code class="literal">
    &lt;
   </code>
   ,
   <code class="literal">
    &lt;=
   </code>
   ,
   <code class="literal">
    =
   </code>
   ,
   <code class="literal">
    &gt;=
   </code>
   ,
   <code class="literal">
    &gt;
   </code>
   , or
   <code class="literal">
    BETWEEN
   </code>
   operators, MySQL normally converts the string to an internal
          long integer for faster comparison (and also for a bit more
   <span class="quote">
    “
    <span class="quote">
     relaxed
    </span>
    ”
   </span>
   string checking). However, this
          conversion is subject to the following exceptions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When you compare two columns
     </p>
    </li>
    <li class="listitem">
     <p>
      When you compare a
      <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>
      , or
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column to an
              expression
     </p>
    </li>
    <li class="listitem">
     <p>
      When you use any comparison method other than those just
              listed, such as
      <code class="literal">
       IN
      </code>
      or
      <a class="link" href="string-comparison-functions.html#function_strcmp">
       <code class="literal">
        STRCMP()
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   For those exceptions, the comparison is done by converting the
          objects to strings and performing a string comparison.
  </p>
  <p>
   To be on the safe side, assume that strings are compared as
          strings and use the appropriate string functions if you want
          to compare a temporal value to a string.
  </p>
  <p>
   The special
   <span class="quote">
    “
    <span class="quote">
     zero
    </span>
    ”
   </span>
   date
   <code class="literal">
    '0000-00-00'
   </code>
   can be stored and retrieved as
   <code class="literal">
    '0000-00-00'.
   </code>
   When a
   <code class="literal">
    '0000-00-00'
   </code>
   date is used through
          Connector/ODBC, it is automatically converted to
   <code class="literal">
    NULL
   </code>
   because ODBC cannot handle that kind
          of date.
  </p>
  <p>
   Because MySQL performs the conversions just described, the
          following statements work (assume that
   <code class="literal">
    idate
   </code>
   is a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78882864"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">19970505</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'19970505'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'97-05-05'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'1997.05.05'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'1997 05 05'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>idate<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'0000-00-00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> idate <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> idate <span class="token operator">&gt;=</span> <span class="token string">'1997-05-05'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> idate <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> idate <span class="token operator">&gt;=</span> <span class="token number">19970505</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">MOD</span><span class="token punctuation">(</span>idate<span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> idate <span class="token operator">&gt;=</span> <span class="token number">19970505</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> idate <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> idate <span class="token operator">&gt;=</span> <span class="token string">'19970505'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, the following statement does not work:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79543227"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> idate <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token function">STRCMP</span><span class="token punctuation">(</span>idate<span class="token punctuation">,</span><span class="token string">'20030505'</span><span class="token punctuation">)</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <a class="link" href="string-comparison-functions.html#function_strcmp">
    <code class="literal">
     STRCMP()
    </code>
   </a>
   is a string function,
          so it converts
   <code class="literal">
    idate
   </code>
   to a string in
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
     </code>
    </em>
    '
   </code>
   format and performs a string comparison. It does not convert
   <code class="literal">
    '20030505'
   </code>
   to the date
   <code class="literal">
    '2003-05-05'
   </code>
   and perform a date comparison.
  </p>
  <p>
   If you enable the
   <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
    <code class="literal">
     ALLOW_INVALID_DATES
    </code>
   </a>
   SQL
          mode, MySQL permits you to store dates that are given only
          limited checking: MySQL requires only that the day is in the
          range from 1 to 31 and the month is in the range from 1 to 12.
          This makes MySQL very convenient for Web applications where
          you obtain year, month, and day in three different fields and
          you want to store exactly what the user inserted (without date
          validation).
  </p>
  <p>
   MySQL permits you to store dates where the day or month and
          day are zero. This is convenient if you want to store a
          birthdate in a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column and
          you know only part of the date. 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>
  <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>
   This is in some cases more convenient than using
   <code class="literal">
    NULL
   </code>
   values. If a date to be stored in a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column cannot be converted
          to any reasonable value, MySQL stores
   <code class="literal">
    '0000-00-00'
   </code>
   . 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>
  <p>
   To have MySQL check all dates and accept only legal dates
          (unless overridden by
   <code class="literal">
    IGNORE
   </code>
   ), set the
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   system variable to
   <code class="literal">
    "NO_ZERO_IN_DATE,NO_ZERO_DATE"
   </code>
   .
  </p>
 </div>
 <br/>
</div>
