SQL Server Date Styles (formats) using CONVERT()

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Published:
Updated:
Browse All Articles > SQL Server Date Styles (formats) using CONVERT()
Perplexed by SQL Server date styles? I sure am. Whilst there's a million tables about this "out there" none seem to suit my desired arrangement. So here is my set of date style tables for SQL Server, organised  by delimiter:
also see: MSSQL 2012 does it better


No Delimiter

StyleExampleLengthSyntax
112200102238convert(varchar(8), your_data_here ,112)
120102236convert(varchar(6), your_data_here ,12)


/ Slash delimited

StyleExampleLengthSyntax
1112001/02/2310convert(varchar(10), your_data_here ,111)
1101/02/238convert(varchar(8), your_data_here ,11)
10102/23/200110convert(varchar(10), your_data_here ,101)
102/23/018convert(varchar(8), your_data_here ,1)
10323/02/200110convert(varchar(10), your_data_here ,103)
323/02/018convert(varchar(8), your_data_here ,3)


. Dot delimited

StyleExampleLengthSyntax
1022001.02.2310convert(varchar(10), your_data_here ,102)
201.02.238convert(varchar(8), your_data_here ,2)
10423.02.200110convert(varchar(10), your_data_here ,104)
423.02.018convert(varchar(8), your_data_here ,4)


- Dash delimited

StyleExampleLengthSyntax
1212001-02-23 04:05:06.00723convert(varchar(23), your_data_here ,121)
1202001-02-23 04:05:0619convert(varchar(19), your_data_here ,120)
11002-23-200110convert(varchar(10), your_data_here ,110)
1002-23-018convert(varchar(8), your_data_here ,10)
10523-02-200110convert(varchar(10), your_data_here ,105)
523-02-018convert(varchar(8), your_data_here ,5)


Space delimited

StyleExampleLengthSyntax
9Feb 23 2001 4:05:06:007AM26convert(varchar(26), your_data_here ,9)
109Feb 23 2001 4:05:06:007AM26convert(varchar(26), your_data_here ,109)
100Feb 23 2001 4:05AM19convert(varchar(19), your_data_here ,100)
107Feb 23, 200112convert(varchar(12), your_data_here ,107)
7Feb 23, 0110convert(varchar(10), your_data_here ,7)
1323 Feb 2001 04:05:06:00724convert(varchar(24), your_data_here ,13)
11323 Feb 2001 04:05:06:00724convert(varchar(24), your_data_here ,113)
10623 Feb 200111convert(varchar(11), your_data_here ,106)
623 Feb 019convert(varchar(9), your_data_here ,6)


Time Only

StyleExampleLengthSyntax
1404:05:06:00712convert(varchar(12), your_data_here ,14)
11404:05:06:00712convert(varchar(12), your_data_here ,114)
804:05:068convert(varchar(8), your_data_here ,8)
10804:05:068convert(varchar(8), your_data_here ,108)

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

13
127,974 Views
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Ask a related question

Comments (4)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
This is damn handy.  Voted Yes.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
HA!  Learn somethign new every day.  You can default an expression using CONVERT
CREATE TABLE STAGING_TABLE (
   Column1 varchar(100) DEFAULT 'PLATTS', 
   Column2 varchar(100) DEFAULT 'A', 
   ColumnDateTime varchar(100) DEFAULT convert(varchar, GETDATE() ,112),  -- < -- Looky here
   Column4 varchar(100))

select * FROM STAGING_TABLE

INSERT INTO STAGING_TABLE (Column4)
VALUES ('foo')

select * FROM STAGING_TABLE

Open in new window

Mike EghtebasDatabase and Application Developer

Commented:
Much better than Google.

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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Today is probably the 100th time I've loaded this article for assistance with a SQL Server date to varchar conversion.

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.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community
Not ready to sign up? Try a week for free to see if you belong