· 8 years ago · Apr 08, 2018, 01:32 PM
1-- Mon Jan 2 15:04:05 -0700 MST 2006
2-- aka: 1/2 3:04:05 PM 2006 -0700 MST
3declare @some_date datetimeoffset = '01-02-2006 15:04:05 +07:00'
4
5drop table if exists #dates
6create table #dates (
7 date_field date
8 ,datetime_field datetime
9 ,datetime2_field datetime2
10 ,datetimeoffset_field datetimeoffset
11 ,time_field time
12)
13
14insert into #dates (
15 date_field, datetime_field, datetime2_field, datetimeoffset_field, time_field
16)
17values (
18 @some_date, @some_date, @some_date, @some_date, @some_date
19)
20
21drop table if exists #date_convert
22create table #date_convert (
23 id int identity(1,1) not null
24 ,convert_code int
25 ,convert_code_group int
26 ,has_date bit
27 ,has_time bit
28 ,has_century bit
29 ,is_nondeterministic bit
30 ,standard_name nvarchar(100)
31 ,output_format nvarchar(100)
32 ,note nvarchar(max)
33)
34
35insert into #date_convert (
36 convert_code
37 ,convert_code_group
38 ,has_date
39 ,has_time
40 ,has_century
41 ,is_nondeterministic
42 ,standard_name
43 ,output_format
44 ,note
45)
46values
47 (0, 0, 1, 1, 1, 1, N'Default for datetime and smalldatetime', N'mon dd yyyy hh:miAM (or PM)', null)
48 ,(100, 0, 1, 1, 1, 1, N'Default for datetime and smalldatetime', N'mon dd yyyy hh:miAM (or PM)', null)
49 ,(1, 1, 1, 0, 0, 1, N'U.S.', N'mm/dd/yy', null)
50 ,(101, 1, 1, 0, 1, 0, N'U.S.', N'mm/dd/yyyy', null)
51 ,(2, 2, 1, 0, 0, 1, N'ANSI', N'yy.mm.dd', null)
52 ,(102, 2, 1, 0, 1, 0, N'ANSI', N'yyyy.mm.dd', null)
53 ,(3, 3, 1, 0, 0, 1, N'British/French', N'dd/mm/yy', null)
54 ,(103, 3, 1, 0, 1, 0, N'British/French', N'dd/mm/yyyy', null)
55 ,(4, 4, 1, 0, 0, 1, N'German', N'dd.mm.yy', null)
56 ,(104, 4, 1, 0, 1, 0, N'German', N'dd.mm.yyyy', null)
57 ,(5, 5, 1, 0, 0, 1, N'Italian', N'dd-mm-yy', null)
58 ,(105, 5, 1, 0, 1, 0, N'Italian', N'dd-mm-yyyy', null)
59 ,(6, 6, 1, 0, 0, 1, N'-', N'dd mon yy', null)
60 ,(106, 6, 1, 0, 1, 1, N'-', N'dd mon yyyy', null)
61 ,(7, 7, 1, 0, 0, 1, N'-', N'Mon dd, yy', null)
62 ,(107, 7, 1, 0, 1, 1, N'-', N'Mon dd, yy', null)
63 ,(8, 8, 0, 1, 0, 1, N'-', N'hh:mi:ss', null)
64 ,(108, 8, 0, 1, 0, 0, N'-', N'hh:mi:ss', null)
65 ,(9, 9, 1, 1, 1, 1, N'Default + milliseconds', N'mon dd yyyy hh:mi:ss:mmmAM (or PM)', null)
66 ,(109, 9, 1, 1, 1, 1, N'Default + milliseconds', N'mon dd yyyy hh:mi:ss:mmmAM (or PM)', null)
67 ,(10, 10, 1, 0, 0, 1, N'USA', N'mm-dd-yy', null)
68 ,(110, 10, 1, 0, 1, 0, N'USA', N'mm-dd-yyyy', null)
69 ,(11, 11, 1, 0, 0, 1, N'JAPAN', N'yy/mm/dd', null)
70 ,(111, 11, 1, 0, 1, 0, N'JAPAN', N'yyyy/mm/dd', null)
71 ,(12, 12, 1, 0, 0, 1, N'ISO', N'yymmdd', null)
72 ,(112, 12, 1, 0, 1, 0, N'ISO', N'yyyymmdd', null)
73 ,(13, 13, 1, 1, 1, 1, N'Europe default + milliseconds', N'dd mon yyyy hh:mi:ss:mmm(24h)', null)
74 ,(113, 13, 1, 1, 1, 1, N'Europe default + milliseconds', N'dd mon yyyy hh:mi:ss:mmm(24h)', null)
75 ,(14, 14, 0, 1, 0, 1, N'-', N'hh:mi:ss:mmm(24h)', null)
76 ,(114, 14, 0, 1, 0, 0, N'-', N'hh:mi:ss:mmm(24h)', null)
77 ,(20, 20, 1, 1, 1, 0, N'ODBC canonical', N'yyyy-mm-dd hh:mi:ss(24h)', null)
78 ,(120, 20, 1, 1, 1, 0, N'ODBC canonical', N'yyyy-mm-dd hh:mi:ss(24h)', null)
79 ,(21, 21, 1, 1, 1, 0, N'ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset', N'yyyy-mm-dd hh:mi:ss.mmm(24h)', null)
80 ,(121, 21, 1, 1, 1, 0, N'ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset', N'yyyy-mm-dd hh:mi:ss.mmm(24h)', null)
81 ,(126, 126, 1, 1, 1, 0, N'ISO8601', N'yyyy-mm-ddThh:mi:ss.mmm (no spaces)', N'Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value ''2012-11-07T18:26:20.000'' is displayed as ''2012-11-07T18:26:20''.')
82 ,(127, 127, 1, 1, 1, 0, N'ISO8601 with time zone Z', N'yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)', N'Note: When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value ''2012-11-07T18:26:20.000'' is displayed as ''2012-11-07T18:26:20''.')
83 ,(130, 130, 1, 1, 1, 1, N'Hijri', N'dd mon yyyy hh:mi:ss:mmmAM', N'In this style, mon represents a multi-token Hijri unicode representation of the full month''s name. This value does not render correctly on a default US installation of SSMS.')
84 ,(131, 131, 1, 1, 1, 0, N'Hijri', N'dd/mm/yyyy hh:mi:ss:mmmAM', null)
85
86select dc.convert_code
87 , dc.standard_name
88 , dc.output_format
89 , convert(varchar(100), d.datetimeoffset_field, dc.convert_code) as example_datetimeoffset
90 , case when has_date = 1 then convert(varchar(100), d.date_field, dc.convert_code) else '' end as example_date
91 , convert(varchar(100), d.datetime_field, dc.convert_code) as example_datetime
92 , convert(varchar(100), d.datetime2_field, dc.convert_code) as example_datetime2
93 , case when has_time = 1 then convert(varchar(100), d.time_field, dc.convert_code) else '' end as example_time
94from #date_convert dc
95cross apply #dates d
96where dc.convert_code not in (130, 131)
97
98drop table if exists #dates
99drop table if exists #date_convert