Format a date and time
Format a date and time.
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'
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.
EXTRACT EXPRESSION as:
EXTRACT(MONTH FROM wk)
other components that may be extracted:
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
In this example we use CAST in order to build up a date in the UK format dd/mm/yyyy
DATE_FORMAT will do the trick:
Specifier | Description |
%a | Abbreviated weekday name (Sun ..Sat )
|
%b | Abbreviated month name (Jan ..Dec )
|
%c | Month, numeric (0 ..12 )
|
%D | Day of the month with English suffix (0th , 1st , 2nd , 3rd , ...)
|
%d | Day of the month, numeric (00 ..31 )
|
%e | Day of the month, numeric (0 ..31 )
|
%f | Microseconds (000000 ..999999 )
|
%H | Hour (00 ..23 )
|
%h | Hour (01 ..12 )
|
%I | Hour (01 ..12 )
|
%i | Minutes, numeric (00 ..59 )
|
%j | Day of year (001 ..366 )
|
%k | Hour (0 ..23 )
|
%l | Hour (1 ..12 )
|
%M | Month name (January ..December )
|
%m | Month, numeric (00 ..12 )
|
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM )
|
%S | Seconds (00 ..59 )
|
%s | Seconds (00 ..59 )
|
%T | Time, 24-hour (hh:mm:ss )
|
%U | Week (00 ..53 ), where Sunday is the first day of the week
|
%u | Week (00 ..53 ), where Monday is the first day of the week
|
%V | Week (01 ..53 ), where Sunday is the first day of the week; used with %X |
%v | Week (01 ..53 ), where Monday is the first day of the week; used with %x |
%W | Weekday name (Sunday ..Saturday )
|
%w | Day of the week (0 =Sunday..6 =Saturday)
|
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric, two digits |
%% | A literal `%'. |
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.
Without century (yy) | With century (yyyy) | Standard | Input/Output** |
---|---|---|---|
- | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | British/French | dd/mm/yy |
4 | 104 | German | dd.mm.yy |
5 | 105 | Italian | dd-mm-yy |
6 | 106 | - | dd mon yy |
7 | 107 | - | Mon dd, yy |
8 | 108 | - | hh:mm:ss |
- | 9 or 109 (*) | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
10 | 110 | USA | mm-dd-yy |
11 | 111 | JAPAN | yy/mm/dd |
12 | 112 | ISO | yymmdd |
- | 13 or 113 (*) | Europe default + milliseconds | dd mon yyyy hh:mm:ss:mmm(24h) |
14 | 114 | - | hh:mi:ss:mmm(24h) |
- | 20 or 120 (*) | ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
- | 21 or 121 (*) | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
- | 126(***) | ISO8601 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
- | 130* | Hijri**** | dd mon yyyy hh:mi:ss:mmmAM |
- | 131* | Hijri**** | dd/mm/yy hh:mi:ss:mmmAM |
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:
<thead></thead><tbody></tbody><tfoot></tfoot><a name="48521"/> Element | <a name="48523"/> Specify in TO_* datetime functions?a | <a name="48525"/> Meaning |
---|---|---|
<a name="48527"/>- <a name="48528"/>/ <a name="48529"/>, <a name="48530"/>. <a name="48531"/>; <a name="48532"/>: <a name="48533"/>"text" | <a name="48535"/> Yes | <a name="48537"/> Punctuation and quoted text is reproduced in the result. |
<a name="48539"/>AD | <a name="48542"/> Yes | <a name="48544"/> AD indicator with or without periods. |
<a name="48546"/>AM | <a name="48549"/> Yes | <a name="48551"/> Meridian indicator with or without periods. |
<a name="48553"/>BC | <a name="48556"/> Yes | <a name="48558"/> BC indicator with or without periods. |
<a name="48560"/>CC | <a name="48563"/> No | <a name="48565"/> Century.
For example, 2002 returns 21; 2000 returns 20. |
<a name="48567"/>D | <a name="48569"/> Yes | <a name="48571"/> Day of week (1-7). |
<a name="48573"/>DAY | <a name="48575"/> Yes | <a name="48577"/> Name of day, padded with blanks to length of 9 characters. |
<a name="48579"/>DD | <a name="48581"/> Yes | <a name="48583"/> Day of month (1-31). |
<a name="48585"/>DDD | <a name="48587"/> Yes | <a name="48589"/> Day of year (1-366). |
<a name="48591"/>DY | <a name="48593"/> Yes | <a name="48595"/> Abbreviated name of day. |
<a name="48597"/>E | <a name="48599"/> No | <a name="48601"/> Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
<a name="48603"/>EE | <a name="48605"/> No | <a name="48607"/> Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
<a name="48609"/><code>FF [1..9] </code> | <a name="48611"/> Yes | <a name="48814"/> Fractional seconds; no radix character is printed (use the Examples:
|
<a name="48617"/>HH | <a name="48619"/> Yes | <a name="48621"/> Hour of day (1-12). |
<a name="48623"/>HH12 | <a name="48625"/> No | <a name="48627"/> Hour of day (1-12). |
<a name="48629"/>HH24 | <a name="48631"/> Yes | <a name="48633"/> Hour of day (0-23). |
<a name="48635"/>IW | <a name="48637"/> No | <a name="48639"/> Week of year (1-52 or 1-53) based on the ISO standard. |
<a name="48641"/>IYY | <a name="48645"/> No | <a name="48647"/> Last 3, 2, or 1 digit(s) of ISO year. |
<a name="48649"/>IYYY | <a name="48651"/> No | <a name="48653"/> 4-digit year based on the ISO standard. |
<a name="48655"/>J | <a name="48657"/> Yes | <a name="48659"/> Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers. |
<a name="48661"/>MI | <a name="48663"/> Yes | <a name="48665"/> Minute (0-59). |
<a name="48667"/>MM | <a name="48669"/> Yes | <a name="48671"/> Month (01-12; JAN = 01). |
<a name="48673"/>MON | <a name="48675"/> Yes | <a name="48677"/> Abbreviated name of month. |
<a name="48679"/>MONTH | <a name="48681"/> Yes | <a name="48683"/> Name of month, padded with blanks to length of 9 characters. |
<a name="48685"/>PM | <a name="48688"/> No | <a name="48690"/> Meridian indicator with or without periods. |
<a name="48692"/>Q | <a name="48694"/> No | <a name="48696"/> Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
<a name="48698"/>RM | <a name="48700"/> Yes | <a name="48702"/> Roman numeral month (I-XII; JAN = I). |
<a name="48704"/>RR | <a name="48706"/> Yes | <a name="51477"/> 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. |
<a name="48710"/>RRRR | <a name="48712"/> Yes | <a name="48714"/> 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. |
<a name="48716"/>SS | <a name="48718"/> Yes | <a name="48720"/> Second (0-59). |
<a name="48722"/>SSSSS | <a name="48724"/> Yes | <a name="48726"/> Seconds past midnight (0-86399). |
<a name="51500"/>TZD | <a name="51502"/> Yes | <a name="51504"/> 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"/> Example: |
<a name="48729"/>TZH | <a name="48731"/> Yes | <a name="48826"/> Time zone hour. (See Example: |
<a name="48736"/>TZM | <a name="48738"/> Yes | <a name="48833"/> Time zone minute. (See Example: |
<a name="51521"/>TZR | <a name="51523"/> Yes | <a name="51525"/> Time zone region information. The value must be one of the time zone regions supported in the database. <a name="51538"/> Example: US/Pacific |
<a name="48743"/>WW | <a name="48745"/> No | <a name="48747"/> 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. |
<a name="48749"/>W | <a name="48751"/> No | <a name="48753"/> Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
<a name="48756"/>X | <a name="48758"/> Yes | <a name="48840"/> Local radix character. <a name="48761"/> Example: |
<a name="48763"/>Y,YYY | <a name="48765"/> Yes | <a name="48767"/> Year with comma in this position. |
<a name="48769"/>YEAR | <a name="48772"/> No | <a name="48774"/> Year, spelled out; "S" prefixes BC dates with "-". |
<a name="48776"/>YYYY | <a name="48779"/> Yes | <a name="48781"/> 4-digit year; "S" prefixes BC dates with "-". |
<a name="48783"/>YYY | <a name="48787"/> Yes | <a name="48789"/> Last 3, 2, or 1 digit(s) of year. |
<a name="48792"/> a The |