"We're trained professionals. (Well, we're semi-trained quasi-professionals, at any rate.)" -Roy Greenhilt, Order of the Stick webcomic
Last time I wrote about converting to and from temporal data types in Oracle. Being how every time I do something for Oracle I have to do the same thing for SQL Server, it's time to delve into temporal data type manipulations in SQL Server.
SQL Server really only has the one function of note that does it all: CONVERT. Personally, I don't find the conversion modifier for convert to be anywhere near as intuitive as to_date or to_char with the format strings that Oracle does - without the chart from the documenation, I'd never be able to remember any of it.
My personal feelings aside, let's hit the base syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
(HTML for this table from SQL Server 2008 Books Online, see link at bottom of post)
Date and Time Styles
When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
|
Without |
With |
Standard |
Input/Output 3 |
|---|---|---|---|
|
- |
0 or 100 (1,2) |
Default |
mon dd yyyy hh:miAM (or PM) |
|
1 |
101 |
U.S. |
mm/dd/yyyy |
|
2 |
102 |
ANSI |
yy.mm.dd |
|
3 |
103 |
British/French |
dd/mm/yyyy |
|
4 |
104 |
German |
dd.mm.yy |
|
5 |
105 |
Italian |
dd-mm-yy |
|
6 |
106 (1) |
- |
dd mon yy |
|
7 |
107 (1) |
- |
Mon dd, yy |
|
8 |
108 |
- |
hh:mi:ss |
|
- |
9 or 109 (1,2) |
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 yyyymmdd |
|
- |
13 or 113 (1, 2) |
Europe default + milliseconds |
dd mon yyyy hh:mi:ss:mmm(24h) |
|
14 |
114 |
- |
hh:mi:ss:mmm(24h) |
|
- |
20 or 120 (2) |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
|
- |
21 or 121 (2) |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
|
- |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
|
- |
127(6, 7) |
ISO8601 with time zone Z. |
yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
|
- |
130 (1, 2) |
Hijri ((5) |
dd mon yyyy hh:mi:ss:mmmAM |
|
- |
131 (2) |
Hijri (5) |
dd/mm/yy hh:mi:ss:mmmAM |
1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.
2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
3 Input when you convert to datetime; output when you convert to character data.
4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.
5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.
| Important |
|---|
|
By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years. |
6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.
When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.
When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.
So, example time!
select convert(char(8), getdate(), 112)
returns
20110929
Shortening the char(8) to char(4) would get us:
select convert(char(4), getdate(), 112)
returns
2011
Changing the convert code from 112 to 12, shortens this to a 2 digit year:
select convert(char(8), getdate(), 12)
returns
110929
References:
SQL Server Books Online: CAST and CONVERT
