Difference between revisions of "Format a date and time"

From SQLZOO
Jump to: navigation, search
(Created page with "Format a date and time. <div class='ht'> <div class=params>schema:scott</div> <div> Many engines support the SQL standard - see Mimer for details. </div> <source lang=sql cla...")
 
 
(One intermediate revision by one user not shown)
Line 1: Line 1:
 
Format a date and time.
 
Format a date and time.
 
<div class='ht'>
 
<div class='ht'>
<div class=params>schema:scott</div>
+
<div class=params>schema:gisq</div>
 
<div>
 
<div>
 
Many engines support the SQL standard - see Mimer for details.
 
Many engines support the SQL standard - see Mimer for details.
Line 47: Line 47:
 
</source>
 
</source>
  
<div class="ecomm e-mysql" style="display: none"></div>
+
<div class="ecomm e-mimer" style="display: none">
 +
Create a string representing a date to a particular format
 +
The Standard provides us with the EXTRACT function that can be
 +
used however many engines provide a more convenient alternative.
 +
<p>[http://sqlzoo.net/sql92.html#extract_expression EXTRACT EXPRESSION] as:<tt>
 +
EXTRACT(MONTH FROM wk)</tt><br/>
 +
other components that may be extracted:
 +
<ul><li>YEAR</li><li>MONTH</li><li>DAY</li><li>HOUR
 +
  </li><li>MINUTE</li></ul></p><p>In this example we use CAST in order to build up a date in the UK format
 +
dd/mm/yyyy</p>
 +
</div>
 +
 
 +
<div class="ecomm e-mysql" style="display: none">
 +
[http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html#idx1386 DATE_FORMAT] will do the trick:
 +
<TABLE BORDER="1"><TR><TD><strong>Specifier</strong></TD><TD><strong>Description</strong></TD></TR><TR><TD><code>%a</code></TD><TD> Abbreviated weekday name (<code>Sun</code>..<code>Sat</code>)
 +
</TD></TR><TR><TD><code>%b</code></TD><TD> Abbreviated month name (<code>Jan</code>..<code>Dec</code>)
 +
</TD></TR><TR><TD><code>%c</code></TD><TD> Month, numeric (<code>0</code>..<code>12</code>)
 +
</TD></TR><TR><TD><code>%D</code></TD><TD> Day of the month with English suffix (<code>0th</code>, <code>1st</code>, <code>2nd</code>, <code>3rd</code>, ...)
 +
</TD></TR><TR><TD><code>%d</code></TD><TD> Day of the month, numeric (<code>00</code>..<code>31</code>)
 +
</TD></TR><TR><TD><code>%e</code></TD><TD> Day of the month, numeric (<code>0</code>..<code>31</code>)
 +
</TD></TR><TR><TD><code>%f</code></TD><TD> Microseconds (<code>000000</code>..<code>999999</code>)
 +
</TD></TR><TR><TD><code>%H</code></TD><TD> Hour (<code>00</code>..<code>23</code>)
 +
</TD></TR><TR><TD><code>%h</code></TD><TD> Hour (<code>01</code>..<code>12</code>)
 +
</TD></TR><TR><TD><code>%I</code></TD><TD> Hour (<code>01</code>..<code>12</code>)
 +
</TD></TR><TR><TD><code>%i</code></TD><TD> Minutes, numeric (<code>00</code>..<code>59</code>)
 +
</TD></TR><TR><TD><code>%j</code></TD><TD> Day of year (<code>001</code>..<code>366</code>)
 +
</TD></TR><TR><TD><code>%k</code></TD><TD> Hour (<code>0</code>..<code>23</code>)
 +
</TD></TR><TR><TD><code>%l</code></TD><TD> Hour (<code>1</code>..<code>12</code>)
 +
</TD></TR><TR><TD><code>%M</code></TD><TD> Month name (<code>January</code>..<code>December</code>)
 +
</TD></TR><TR><TD><code>%m</code></TD><TD> Month, numeric (<code>00</code>..<code>12</code>)
 +
</TD></TR><TR><TD><code>%p</code></TD><TD><code>AM</code> or <code>PM</code></TD></TR><TR><TD><code>%r</code></TD><TD> Time, 12-hour (<code>hh:mm:ss</code> followed by <code>AM</code> or <code>PM</code>)
 +
</TD></TR><TR><TD><code>%S</code></TD><TD> Seconds (<code>00</code>..<code>59</code>)
 +
</TD></TR><TR><TD><code>%s</code></TD><TD> Seconds (<code>00</code>..<code>59</code>)
 +
</TD></TR><TR><TD><code>%T</code></TD><TD> Time, 24-hour (<code>hh:mm:ss</code>)
 +
</TD></TR><TR><TD><code>%U</code></TD><TD> Week (<code>00</code>..<code>53</code>), where Sunday is the first day of the week
 +
</TD></TR><TR><TD><code>%u</code></TD><TD> Week (<code>00</code>..<code>53</code>), where Monday is the first day of the week
 +
</TD></TR><TR><TD><code>%V</code></TD><TD> Week (<code>01</code>..<code>53</code>), where Sunday is the first day of the week; used with <code>%X</code></TD></TR><TR><TD><code>%v</code></TD><TD> Week (<code>01</code>..<code>53</code>), where Monday is the first day of the week; used with <code>%x</code></TD></TR><TR><TD><code>%W</code></TD><TD> Weekday name (<code>Sunday</code>..<code>Saturday</code>)
 +
</TD></TR><TR><TD><code>%w</code></TD><TD> Day of the week (<code>0</code>=Sunday..<code>6</code>=Saturday)
 +
</TD></TR><TR><TD><code>%X</code></TD><TD> Year for the week where Sunday is the first day of the week, numeric, four digits; used with <code>%V</code></TD></TR><TR><TD><code>%x</code></TD><TD> Year for the week, where Monday is the first day of the week, numeric, four digits; used with <code>%v</code></TD></TR><TR><TD><code>%Y</code></TD><TD> Year, numeric, four digits
 +
</TD></TR><TR><TD><code>%y</code></TD><TD> Year, numeric, two digits
 +
</TD></TR><TR><TD><code>%%</code></TD><TD> A literal <samp>`%'</samp>.
 +
</TD></TR></TABLE>
 +
</div>
 +
 
 +
<div class="ecomm e-sqlserver" style="display: none">
 +
SQL Server has inexplicably limited support for formatting dates.
 +
I am almost certain that there is a better way to do this - please help!
 +
The <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp">CONVERT</a> function may be used.
 +
<TABLE border="1" cellpadding="2" cols="4" frame="box" rules="all" width="571"><TR VALIGN="top"><TH class="label" width="23%">Without century (yy)</TH><TH class="label" width="21%">With century (yyyy)</TH><TH class="label" width="27%"><BR/>
 +
Standard</TH><TH class="label" width="29%"><BR/>
 +
Input/Output**</TH></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">0 or 100 (*) </TD><TD width="27%">Default</TD><TD width="29%">mon dd yyyy hh:miAM (or PM)</TD></TR><TR VALIGN="top"><TD width="23%">1</TD><TD width="21%">101</TD><TD width="27%">USA</TD><TD width="29%">mm/dd/yy</TD></TR><TR VALIGN="top"><TD width="23%">2</TD><TD width="21%">102</TD><TD width="27%">ANSI</TD><TD width="29%">yy.mm.dd</TD></TR><TR VALIGN="top"><TD width="23%">3</TD><TD width="21%">103</TD><TD width="27%">British/French</TD><TD width="29%">dd/mm/yy</TD></TR><TR VALIGN="top"><TD width="23%">4</TD><TD width="21%">104</TD><TD width="27%">German</TD><TD width="29%">dd.mm.yy</TD></TR><TR VALIGN="top"><TD width="23%">5</TD><TD width="21%">105</TD><TD width="27%">Italian</TD><TD width="29%">dd-mm-yy</TD></TR><TR VALIGN="top"><TD width="23%">6</TD><TD width="21%">106</TD><TD width="27%">-</TD><TD width="29%">dd mon yy</TD></TR><TR VALIGN="top"><TD width="23%">7</TD><TD width="21%">107</TD><TD width="27%">-</TD><TD width="29%">Mon dd, yy</TD></TR><TR VALIGN="top"><TD width="23%">8</TD><TD width="21%">108</TD><TD width="27%">-</TD><TD width="29%">hh:mm:ss</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">9 or 109 (*) </TD><TD width="27%">Default + milliseconds</TD><TD width="29%">mon dd yyyy hh:mi:ss:mmmAM (or PM)</TD></TR><TR VALIGN="top"><TD width="23%">10</TD><TD width="21%">110</TD><TD width="27%">USA</TD><TD width="29%">mm-dd-yy</TD></TR><TR VALIGN="top"><TD width="23%">11</TD><TD width="21%">111</TD><TD width="27%">JAPAN</TD><TD width="29%">yy/mm/dd</TD></TR><TR VALIGN="top"><TD width="23%">12</TD><TD width="21%">112</TD><TD width="27%">ISO</TD><TD width="29%">yymmdd</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">13 or 113 (*) </TD><TD width="27%">Europe default + milliseconds</TD><TD width="29%">dd mon yyyy hh:mm:ss:mmm(24h)</TD></TR><TR VALIGN="top"><TD width="23%">14</TD><TD width="21%">114</TD><TD width="27%">-</TD><TD width="29%">hh:mi:ss:mmm(24h)</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">20 or 120 (*) </TD><TD width="27%">ODBC canonical</TD><TD width="29%">yyyy-mm-dd hh:mi:ss(24h)</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">21 or 121 (*) </TD><TD width="27%">ODBC canonical (with milliseconds)</TD><TD width="29%">yyyy-mm-dd hh:mi:ss.mmm(24h)</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">126(***)</TD><TD width="27%">ISO8601</TD><TD width="29%">yyyy-mm-dd Thh:mm:ss.mmm(no spaces)</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">130*</TD><TD width="27%">Hijri****</TD><TD width="29%">dd mon yyyy hh:mi:ss:mmmAM</TD></TR><TR VALIGN="top"><TD width="23%">-</TD><TD width="21%">131*</TD><TD width="27%">Hijri****</TD><TD width="29%">dd/mm/yy hh:mi:ss:mmmAM</TD></TR></TABLE>
 +
</div>
 +
 
 +
<div class="ecomm e-oracle" style="display: none">
 +
The function <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions187.htm#i1003589">TO_DATE</a> and <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions184.htm#i1009324">TO_CHAR</a> and the
 +
<a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions001.htm#i88891">Datetime Format Elements</a> is useful here:
 +
<table summary="" class="HRuleFormal" border="1" frame="HSIDES" rules="ROWS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""><thead><tr class="Formal"><th class="Formal" align="left" valign="bottom" scope="col"><a name="48521"/><p class="THC" align="center"><font face="Arial, Helvetica, sans-serif"><strong>Element</strong></font></p></th><th class="Formal" align="left" valign="bottom" scope="col"><a name="48523"/><p class="THC" align="center"><font face="Arial, Helvetica, sans-serif"><strong>Specify in TO_* datetime functions?<sup>a</sup></strong></font></p></th><th class="Formal" align="left" valign="bottom" scope="col"><a name="48525"/><p class="THC" align="center"><font face="Arial, Helvetica, sans-serif"><strong>Meaning</strong></font></p></th></tr></thead><tbody><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48527"/>-
 +
<a name="48528"/>/
 +
<a name="48529"/>,
 +
<a name="48530"/>.
 +
<a name="48531"/>;
 +
<a name="48532"/>:
 +
<a name="48533"/>"text"
 +
</pre></td><td class="Formal"><a name="48535"/><p class="TB">Yes</p></td><td class="Formal"><a name="48537"/><p class="TB">Punctuation and quoted text is reproduced in the result.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48539"/>AD
 +
<a name="48540"/>A.D.
 +
</pre></td><td class="Formal"><a name="48542"/><p class="TB">Yes</p></td><td class="Formal"><a name="48544"/><p class="TB">AD indicator with or without periods.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48546"/>AM
 +
<a name="48547"/>A.M.
 +
</pre></td><td class="Formal"><a name="48549"/><p class="TB">Yes</p></td><td class="Formal"><a name="48551"/><p class="TB">Meridian indicator with or without periods.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48553"/>BC
 +
<a name="48554"/>B.C.
 +
</pre></td><td class="Formal"><a name="48556"/><p class="TB">Yes</p></td><td class="Formal"><a name="48558"/><p class="TB">BC indicator with or without periods.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48560"/>CC
 +
<a name="48561"/>SCC
 +
</pre></td><td class="Formal"><a name="48563"/><p class="TB">No</p></td><td class="Formal"><a name="48565"/><p class="TB">Century.</p><ul class="TLB"><li class="TLB" type="disc"><a name="115985"/>If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.</li><li class="TLB" type="disc"><a name="115990"/>If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.</li></ul><a name="116001"/><p class="TB">For example, 2002 returns 21; 2000 returns 20.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48567"/>D
 +
</pre></td><td class="Formal"><a name="48569"/><p class="TB">Yes</p></td><td class="Formal"><a name="48571"/><p class="TB">Day of week (1-7).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48573"/>DAY
 +
</pre></td><td class="Formal"><a name="48575"/><p class="TB">Yes</p></td><td class="Formal"><a name="48577"/><p class="TB">Name of day, padded with blanks to length of 9 characters.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48579"/>DD
 +
</pre></td><td class="Formal"><a name="48581"/><p class="TB">Yes</p></td><td class="Formal"><a name="48583"/><p class="TB">Day of month (1-31).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48585"/>DDD
 +
</pre></td><td class="Formal"><a name="48587"/><p class="TB">Yes</p></td><td class="Formal"><a name="48589"/><p class="TB">Day of year (1-366).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48591"/>DY
 +
</pre></td><td class="Formal"><a name="48593"/><p class="TB">Yes</p></td><td class="Formal"><a name="48595"/><p class="TB">Abbreviated name of day.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48597"/>E
 +
</pre></td><td class="Formal"><a name="48599"/><p class="TB">No</p></td><td class="Formal"><a name="48601"/><p class="TB">Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48603"/>EE
 +
</pre></td><td class="Formal"><a name="48605"/><p class="TB">No</p></td><td class="Formal"><a name="48607"/><p class="TB">Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48609"/><code>FF [1..9]
 +
</code></pre></td><td class="Formal"><a name="48611"/><p class="TB">Yes</p></td><td class="Formal"><a name="48814"/><p class="TB">Fractional seconds; no radix character is printed (use the <code>X</code> format element to add the radix character). Use the numbers 1 to 9 after <code>FF</code> to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle uses the precision specified for the datetime datatype or the datatype's default precision. <a name="77614"/></p><p class="TB"><strong class="Bold">Examples:</strong><code>'HH:MI:SS.FF'</code><a name="77613"/></p><p class="TB"><code>SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;</code></p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48617"/>HH
 +
</pre></td><td class="Formal"><a name="48619"/><p class="TB">Yes</p></td><td class="Formal"><a name="48621"/><p class="TB">Hour of day (1-12).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48623"/>HH12
 +
</pre></td><td class="Formal"><a name="48625"/><p class="TB">No</p></td><td class="Formal"><a name="48627"/><p class="TB">Hour of day (1-12).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48629"/>HH24
 +
</pre></td><td class="Formal"><a name="48631"/><p class="TB">Yes</p></td><td class="Formal"><a name="48633"/><p class="TB">Hour of day (0-23).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48635"/>IW
 +
</pre></td><td class="Formal"><a name="48637"/><p class="TB">No</p></td><td class="Formal"><a name="48639"/><p class="TB">Week of year (1-52 or 1-53) based on the ISO standard.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48641"/>IYY
 +
<a name="48642"/>IY
 +
<a name="48643"/>I
 +
</pre></td><td class="Formal"><a name="48645"/><p class="TB">No</p></td><td class="Formal"><a name="48647"/><p class="TB">Last 3, 2, or 1 digit(s) of ISO year.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48649"/>IYYY
 +
</pre></td><td class="Formal"><a name="48651"/><p class="TB">No</p></td><td class="Formal"><a name="48653"/><p class="TB">4-digit year based on the ISO standard.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48655"/>J
 +
</pre></td><td class="Formal"><a name="48657"/><p class="TB">Yes</p></td><td class="Formal"><a name="48659"/><p class="TB">Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48661"/>MI
 +
</pre></td><td class="Formal"><a name="48663"/><p class="TB">Yes</p></td><td class="Formal"><a name="48665"/><p class="TB">Minute (0-59).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48667"/>MM
 +
</pre></td><td class="Formal"><a name="48669"/><p class="TB">Yes</p></td><td class="Formal"><a name="48671"/><p class="TB">Month (01-12; JAN = 01).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48673"/>MON
 +
</pre></td><td class="Formal"><a name="48675"/><p class="TB">Yes</p></td><td class="Formal"><a name="48677"/><p class="TB">Abbreviated name of month.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48679"/>MONTH
 +
</pre></td><td class="Formal"><a name="48681"/><p class="TB">Yes</p></td><td class="Formal"><a name="48683"/><p class="TB">Name of month, padded with blanks to length of 9 characters.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48685"/>PM
 +
<a name="48686"/>P.M.
 +
</pre></td><td class="Formal"><a name="48688"/><p class="TB">No</p></td><td class="Formal"><a name="48690"/><p class="TB">Meridian indicator with or without periods.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48692"/>Q
 +
</pre></td><td class="Formal"><a name="48694"/><p class="TB">No</p></td><td class="Formal"><a name="48696"/><p class="TB">Quarter of year (1, 2, 3, 4; JAN-MAR = 1).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48698"/>RM
 +
</pre></td><td class="Formal"><a name="48700"/><p class="TB">Yes</p></td><td class="Formal"><a name="48702"/><p class="TB">Roman numeral month (I-XII; JAN = I).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48704"/>RR
 +
</pre></td><td class="Formal"><a name="48706"/><p class="TB">Yes</p></td><td class="Formal"><a name="51477"/><p class="TB">Lets you store 20th century dates in the 21st century using only two digits. See <a href="sql_elements4a.htm#35239">"The RR Date Format Element"</a><a href="sql_elements4a.htm#35239"/> for detailed information.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48710"/>RRRR
 +
</pre></td><td class="Formal"><a name="48712"/><p class="TB">Yes</p></td><td class="Formal"><a name="48714"/><p class="TB">Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48716"/>SS
 +
</pre></td><td class="Formal"><a name="48718"/><p class="TB">Yes</p></td><td class="Formal"><a name="48720"/><p class="TB">Second (0-59).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48722"/>SSSSS
 +
</pre></td><td class="Formal"><a name="48724"/><p class="TB">Yes</p></td><td class="Formal"><a name="48726"/><p class="TB">Seconds past midnight (0-86399).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="51500"/>TZD
 +
</pre></td><td class="Formal"><a name="51502"/><p class="TB">Yes</p></td><td class="Formal"><a name="51504"/><p class="TB">Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. <a name="51511"/></p><p class="TB"><strong class="Bold">Example:</strong><code>PST</code> (for US/Pacific standard time); <code>PDT</code> (for US/Pacific daylight time).</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48729"/>TZH
 +
</pre></td><td class="Formal"><a name="48731"/><p class="TB">Yes</p></td><td class="Formal"><a name="48826"/><p class="TB">Time zone hour. (See <code>TZM</code> format element.) <a name="48734"/></p><p class="TB"><strong class="Bold">Example:</strong><code>'HH:MI:SS.FFTZH:TZM'</code>.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48736"/>TZM
 +
</pre></td><td class="Formal"><a name="48738"/><p class="TB">Yes</p></td><td class="Formal"><a name="48833"/><p class="TB">Time zone minute. (See <code>TZH</code> format element.) <a name="48741"/></p><p class="TB"><strong class="Bold">Example:</strong><code>'HH:MI:SS.FFTZH:TZM'</code>.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="51521"/>TZR
 +
</pre></td><td class="Formal"><a name="51523"/><p class="TB">Yes</p></td><td class="Formal"><a name="51525"/><p class="TB">Time zone region information. The value must be one of the time zone regions supported in the database. <a name="51538"/></p><p class="TB"><strong class="Bold">Example:</strong> US/Pacific</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48743"/>WW
 +
</pre></td><td class="Formal"><a name="48745"/><p class="TB">No</p></td><td class="Formal"><a name="48747"/><p class="TB">Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48749"/>W
 +
</pre></td><td class="Formal"><a name="48751"/><p class="TB">No</p></td><td class="Formal"><a name="48753"/><p class="TB">Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48756"/>X
 +
</pre></td><td class="Formal"><a name="48758"/><p class="TB">Yes</p></td><td class="Formal"><a name="48840"/><p class="TB">Local radix character. <a name="48761"/></p><p class="TB"><strong class="Bold">Example:</strong><code>'HH:MI:SSXFF'</code>.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48763"/>Y,YYY
 +
</pre></td><td class="Formal"><a name="48765"/><p class="TB">Yes</p></td><td class="Formal"><a name="48767"/><p class="TB">Year with comma in this position.</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48769"/>YEAR
 +
<a name="48770"/>SYEAR
 +
</pre></td><td class="Formal"><a name="48772"/><p class="TB">No</p></td><td class="Formal"><a name="48774"/><p class="TB">Year, spelled out; "S" prefixes BC dates with "-".</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48776"/>YYYY
 +
<a name="48777"/>SYYYY
 +
</pre></td><td class="Formal"><a name="48779"/><p class="TB">Yes</p></td><td class="Formal"><a name="48781"/><p class="TB">4-digit year; "S" prefixes BC dates with "-".</p></td></tr><tr class="Formal" align="left" valign="top"><td class="Formal"><pre class="TCI"><a name="48783"/>YYY
 +
<a name="48784"/>YY
 +
<a name="48785"/>Y
 +
</pre></td><td class="Formal"><a name="48787"/><p class="TB">Yes</p></td><td class="Formal"><a name="48789"/><p class="TB">Last 3, 2, or 1 digit(s) of year.</p></td></tr></tbody><tfoot><tr class="Footer" align="left" valign="top"><td class="Footer" colspan="3" rowspan="1"><a name="48792"/><p class="TB"><sup>a</sup> The <code>TO_</code>* datetime functions are <code>TO_CHAR</code>, <code>TO_DATE</code>, <code>TO_TIMESTAMP</code>, <code>TO_TIMESTAMP_TZ</code>, <code>TO_YMINTERVAL</code>, and <code>TO_DSINTERVAL</code>.</p></td></tr></tfoot></table>
 +
</div>
 +
<div class="ecomm e-access" style="display: none">Use the FORMAT function
 +
</div>
 +
<div class="ecomm e-db2" style="display: none">
 +
The [https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0411 CHAR]
 +
function will convert to any of the following formats:
 +
ISO, USA, EUR, JIS, and LOCAL
 +
</div>
 +
 
 
</div>
 
</div>
  
 
{{DATE and TIME ref}}
 
{{DATE and TIME ref}}

Latest revision as of 12:02, 8 August 2012

Format a date and time.

schema:gisq

Many engines support the SQL standard - see Mimer for details.

DROP TABLE t_peep;
CREATE TABLE t_peep
 (id INTEGER PRIMARY KEY
 ,name VARCHAR(50))
SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
       || '/' ||
       CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
       || '/' ||
       CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
       song
 FROM totp
 WHERE singer='Tom Jones'
SELECT STRFTIME('%d/%m/%Y', wk), song
 FROM totp
 WHERE singer='Tom Jones'
SELECT DATE_FORMAT(wk,'%d/%m/%Y'), song
FROM totp
WHERE singer='Tom Jones'
SELECT CONVERT(VARCHAR(10), wk, 103), song
  FROM totp
 WHERE singer='Tom Jones'
SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
FROM totp
WHERE singer='Tom Jones'
SELECT FORMAT(wk, 'DD/MM/YYYY'),
       song
 FROM totp
 WHERE singer='Tom Jones'
SELECT CHAR(wk, eur),
       song
 FROM totp
 WHERE singer='Tom Jones'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense