XML:
ISO8601 format (style number 126)
e.g.
2013-09-21T13:14:15.123
convert(datetime, your_data_here ,126)
convert(datetime, your_data_here ,127) -- if there is time zone info
CAST and CONVERT (Transact-SQL)
SQL 2012? Some new Format sample can be found here
DIY Table:
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
Select all Open in new window
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
Select all Open in new window
The ultimate guide to the datetime datatypes
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
http://www.karaszi.com/SQLServer/info_datetime.asp
"I've found that the DATEADD technique significantly outperforms the string method when the expression is applied to a column in large queries. For example, I often use the DATEADD method to group by date when the datetime column includes a time component. I haven't tested the performance recently but I recall it was minutes in some cases."
Dan is someone I trust very much, so I guess I have some re-learning to do (start using the DATEADD method instead of the string method). Dan also followed up with a script file showing this. Here it is (with some minor formatting modifications made by me). My tests show consistent results on 2005 and 2008 where for 10,000,000 rows the DATEADD version took about 6 seconds and the string version took about 13 seconds. Dan reported even greater difference on 2000.
time in AM/PM - how hard does this have to be? yikes! go for SQL 2012 instead.
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
Select all Open in new window
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: