PATTERN STYLED DATE SYNTAX STYLE LENGTH
YYYY MM DD 20010223 convert(varchar, your_data_here ,112) 112 8
YY MM DD 010223 convert(varchar, your_data_here ,12) 12 6
/ Slash delimitedPATTERN STYLED DATE SYNTAX STYLE LENGTH
YYYY MM DD 2001/02/23 convert(varchar, your_data_here ,111) 111 10
YY MM DD 01/02/23 convert(varchar, your_data_here ,11) 11 8
MM DD YYYY 02/23/2001 convert(varchar, your_data_here ,101) 101 10
MM DD YY 02/23/01 convert(varchar, your_data_here ,1) 1 8
DD MM YYYY 23/02/2001 convert(varchar, your_data_here ,103) 103 10
DD MM YY 23/02/01 convert(varchar, your_data_here ,3) 3 8
. Dot delimitedPATTERN STYLED DATE SYNTAX STYLE LENGTH
YYYY MM DD 2001.02.23 convert(varchar, your_data_here ,102) 102 10
YY MM DD 01.02.23 convert(varchar, your_data_here ,2) 2 8
DD MM YYYY 23.02.2001 convert(varchar, your_data_here ,104) 104 10
DD MM YY 23.02.01 convert(varchar, your_data_here ,4) 4 8
- Dash delimitedPATTERN STYLED DATE SYNTAX STYLE LENGTH
YYYY MM DD 2001-02-23 04:05:06.007 convert(varchar, your_data_here ,121) 121 23
YYYY MM DD 2001-02-23 04:05:06 convert(varchar, your_data_here ,120) 120 19
MM DD YYYY 02-23-2001 convert(varchar, your_data_here ,110) 110 10
MM DD YY 02-23-01 convert(varchar, your_data_here ,10) 10 8
DD MM YYYY 23-02-2001 convert(varchar, your_data_here ,105) 105 10
DD MM YY 23-02-01 convert(varchar, your_data_here ,5) 5 8
Space delimitedPATTERN STYLED DATE SYNTAX STYLE LENGTH
MMM DD YYYY Feb 23 2001 4:05:06:007AM convert(varchar, your_data_here ,9) 9 26
MMM DD YYYY Feb 23 2001 4:05:06:007AM convert(varchar, your_data_here ,109) 109 26
MMM DD YYYY Feb 23 2001 4:05AM convert(varchar, your_data_here ,100) 100 19
MMM DD YYYY Feb 23, 2001 convert(varchar, your_data_here ,107) 107 12
MMM DD YY Feb 23, 01 convert(varchar, your_data_here ,7) 7 10
DD MMM YYYY 23 Feb 2001 04:05:06:007 convert(varchar, your_data_here ,13) 13 24
DD MMM YYYY 23 Feb 2001 04:05:06:007 convert(varchar, your_data_here ,113) 113 24
DD MM YYYY 23 Feb 2001 convert(varchar, your_data_here ,106) 106 11
DD MM YY 23 Feb 01 convert(varchar, your_data_here ,6) 6 9
Time OnlyPATTERN STYLED DATE SYNTAX STYLE LENGTH
hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,14) 14 12
hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,114) 114 12
hh:mm:ss 04:05:06 convert(varchar, your_data_here ,8) 8 8
hh:mm:ss 04:05:06 convert(varchar, your_data_here ,108) 108 8
DELIMITED STARTS PATTERN STYLED DATE SYNTAX STYLE LENGTH
YYYY YYYY MM DD 20010223 convert(varchar, your_data_here ,112) 112 8
YY YY MM DD 010223 convert(varchar, your_data_here ,12) 12 6
slash YYYY YYYY MM DD 2001/02/23 convert(varchar, your_data_here ,111) 111 10
slash YY YY MM DD 01/02/23 convert(varchar, your_data_here ,11) 11 8
slash MM MM DD YYYY 02/23/2001 convert(varchar, your_data_here ,101) 101 10
slash MM MM DD YY 02/23/01 convert(varchar, your_data_here ,1) 1 8
slash DD DD MM YYYY 23/02/2001 convert(varchar, your_data_here ,103) 103 10
slash DD DD MM YY 23/02/01 convert(varchar, your_data_here ,3) 3 8
dot YYYY YYYY MM DD 2001.02.23 convert(varchar, your_data_here ,102) 102 10
dot YY YY MM DD 01.02.23 convert(varchar, your_data_here ,2) 2 8
dot DD DD MM YYYY 23.02.2001 convert(varchar, your_data_here ,104) 104 10
dot DD DD MM YY 23.02.01 convert(varchar, your_data_here ,4) 4 8
dash YYYY YYYY MM DD 2001-02-23 04:05:06.007 convert(varchar, your_data_here ,121) 121 23
dash YYYY YYYY MM DD 2001-02-23 04:05:06 convert(varchar, your_data_here ,120) 120 19
dash MM MM DD YYYY 02-23-2001 convert(varchar, your_data_here ,110) 110 10
dash MM MM DD YY 02-23-01 convert(varchar, your_data_here ,10) 10 8
dash DD DD MM YYYY 23-02-2001 convert(varchar, your_data_here ,105) 105 10
dash DD DD MM YY 23-02-01 convert(varchar, your_data_here ,5) 5 8
space MMM MMM DD YYYY Feb 23 2001 4:05:06:007AM convert(varchar, your_data_here ,9) 9 26
space MMM MMM DD YYYY Feb 23 2001 4:05:06:007AM convert(varchar, your_data_here ,109) 109 26
space MMM MMM DD YYYY Feb 23 2001 4:05AM convert(varchar, your_data_here ,100) 100 19
space MMM MMM DD YYYY Feb 23, 2001 convert(varchar, your_data_here ,107) 107 12
space MMM MMM DD YY Feb 23, 01 convert(varchar, your_data_here ,7) 7 10
space DD DD MMM YYYY 23 Feb 2001 04:05:06:007 convert(varchar, your_data_here ,13) 13 24
space DD DD MMM YYYY 23 Feb 2001 04:05:06:007 convert(varchar, your_data_here ,113) 113 24
space DD DD MM YYYY 23 Feb 2001 convert(varchar, your_data_here ,106) 106 11
space DD DD MM YY 23 Feb 01 convert(varchar, your_data_here ,6) 6 9
colon hh hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,14) 14 12
colon hh hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,114) 114 12
colon hh hh:mm:ss 04:05:06 convert(varchar, your_data_here ,8) 8 8
colon hh hh:mm:ss 04:05:06 convert(varchar, your_data_here ,108) 108 8
for those wishing to make their own table, here's how I derived mine.
declare @Your_Data_Here as datetime
set @Your_Data_Here = '2001-02-23 04:05:06:007'
select
starts
, delimited
, case
when starts = '. Hijri' then '. Hijri'
when starts = 'YYYY' and Style in (121,120,102,112,111) then 'YYYY MM DD'
when starts = 'YY' and Style in (2,11,12) then 'YY MM DD'
when starts = 'MMM' and charindex('2001',Styled_Date) > 1 then 'MMM DD YYYY'
when starts = 'MMM' and charindex('2001',Styled_Date) < 1 then 'MMM DD YY'
when starts = 'MM' and charindex('2001',Styled_Date) > 1 then 'MM DD YYYY'
when starts = 'MM' and charindex('2001',Styled_Date) < 1 then 'MM DD YY'
when starts = 'DD' and right(Styled_Date,4) ='2001' then 'DD MM YYYY'
when starts = 'DD' and right(Styled_Date,2) ='01' then 'DD MM YY'
when starts = 'DD' and charindex('Feb',Styled_Date) > 1 then 'DD MMM YYYY'
when charindex('007',Styled_Date) > 1 then 'hh:mm:ss:ms'
else 'hh:mm:ss'
end as pattern
, Styled_Date
--, '''' + Styled_Date_$_Excel
, 'convert(varchar'
--+ '(' + cast(length as varchar) + ')'
+ ', your_data_here ,'
+ cast(Style as varchar)
+ ')'
as syntax
, Style
, length
, delimiter
from (
select
Styled_Date
, Style
, case
when left(Styled_Date,3) = 'Feb' then 'MMM'
when left(Styled_Date,4) = '2001' then 'YYYY'
when left(Styled_Date,2) = '01' then 'YY'
when left(Styled_Date,2) = '02' then 'MM'
when left(Styled_Date,2) = '23' then 'DD'
when left(Styled_Date,2) = '04' then 'hh'
when left(Styled_Date,2) = '05' then 'mm'
when left(Styled_Date,2) = '06' then 'ss'
when left(Styled_Date,2) = '07' then 'ms'
else '. Hijri'
end as starts
, len(Styled_Date) length
, case
when charindex('/',Styled_Date) > 1 then 'slash'
when charindex('-',Styled_Date) > 1 then 'dash'
when charindex('.',Styled_Date) > 1 then 'dot'
when charindex(' ',Styled_Date) > 1 then 'space'
when charindex(',',Styled_Date) > 1 then 'comma'
when charindex(':',Styled_Date) > 1 then 'colon'
else ''
end as delimited
, case
when Style in(12,112) then '0'
when charindex('/',Styled_Date) > 1 then '/'
when charindex('-',Styled_Date) > 1 then '-'
when charindex('.',Styled_Date) > 1 then '.'
when charindex(' ',Styled_Date) < 1 then null
--when charindex(',',Styled_Date) > 1 then ','
--when charindex(':',Styled_Date) > 1 then ':'
else ' '
end as delimiter
from (
select convert(varchar,@Your_Data_Here,1) as Styled_Date, 1 as Style union all
select convert(varchar(30),@Your_Data_Here,2) as Styled_Date, 2 as Style union all
select convert(varchar(30),@Your_Data_Here,3) as Styled_Date, 3 as Style union all
select convert(varchar(30),@Your_Data_Here,4) as Styled_Date, 4 as Style union all
select convert(varchar(30),@Your_Data_Here,5) as Styled_Date, 5 as Style union all
select convert(varchar(30),@Your_Data_Here,6) as Styled_Date, 6 as Style union all
select convert(varchar(30),@Your_Data_Here,7) as Styled_Date, 7 as Style union all
select convert(varchar(30),@Your_Data_Here,8) as Styled_Date, 8 as Style union all
select convert(varchar(30),@Your_Data_Here,9) as Styled_Date, 9 as Style union all
select convert(varchar(30),@Your_Data_Here,10) as Styled_Date, 10 as Style union all
select convert(varchar(30),@Your_Data_Here,11) as Styled_Date, 11 as Style union all
select convert(varchar(30),@Your_Data_Here,12) as Styled_Date, 12 as Style union all
select convert(varchar(30),@Your_Data_Here,13) as Styled_Date, 13 as Style union all
select convert(varchar(30),@Your_Data_Here,14) as Styled_Date, 14 as Style union all
select convert(varchar(30),@Your_Data_Here,100) as Styled_Date, 100 as Style union all
select convert(varchar(30),@Your_Data_Here,101) as Styled_Date, 101 as Style union all
select convert(varchar(30),@Your_Data_Here,102) as Styled_Date, 102 as Style union all
select convert(varchar(30),@Your_Data_Here,103) as Styled_Date, 103 as Style union all
select convert(varchar(30),@Your_Data_Here,104) as Styled_Date, 104 as Style union all
select convert(varchar(30),@Your_Data_Here,105) as Styled_Date, 105 as Style union all
select convert(varchar(30),@Your_Data_Here,106) as Styled_Date, 106 as Style union all
select convert(varchar(30),@Your_Data_Here,107) as Styled_Date, 107 as Style union all
select convert(varchar(30),@Your_Data_Here,108) as Styled_Date, 108 as Style union all
select convert(varchar(30),@Your_Data_Here,109) as Styled_Date, 109 as Style union all
select convert(varchar(30),@Your_Data_Here,110) as Styled_Date, 110 as Style union all
select convert(varchar(30),@Your_Data_Here,111) as Styled_Date, 111 as Style union all
select convert(varchar(30),@Your_Data_Here,112) as Styled_Date, 112 as Style union all
select convert(varchar(30),@Your_Data_Here,113) as Styled_Date, 113 as Style union all
select convert(varchar(30),@Your_Data_Here,114) as Styled_Date, 114 as Style union all
select convert(varchar(30),@Your_Data_Here,120) as Styled_Date, 120 as Style union all
select convert(varchar(30),@Your_Data_Here,121) as Styled_Date, 121 as Style
--union all
--select convert(varchar(30),@Your_Data_Here,130) as Styled_Date, 130 as Style union all
--select convert(varchar(30),@Your_Data_Here,131) as Styled_Date, 131 as Style
) innerq
) derived
order by
delimiter desc
, starts desc
, length desc
, Style
right( convert(varchar, the_time ,100) , 7)
, replace( replace( right( convert(varchar, the_time ,100) , 7), 'AM', ' AM'), 'PM', ' PM')
http://sqlfiddle.com/#!3/9707a/1
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
Commented:
Open in new window
Commented:
Thanks to Paul for this valuable article.
Thanks to Jim, for repeatedly referencing this article in different posts. I will do the same from now on so people discover what they are missing.
Mike
Commented: