<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Server Date Styles (formats) using CONVERT()

Published on
142,117 Points
4 Comments
105,817 Views
13 Endorsements
Last Modified:
Approved
PortletPaul
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Perplexed by SQL Server date styles? I sure am. There's a million tables of the above out there but none seem to suit my desired arrangement. To me the delimiter decision is needed first (to avoid mistakes of using a dot when you wanted a dash), then which time unit commences the required output (and this also arranged in descending order of unit size) {i.e. 'big-endian' thinking at work}. So for those stuck with SQL Server before the Format function, there it is: My set of date style tables for SQL Server.

also see: MSSQL 2012 does it better

No Delimiter
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

Open in new window

/ Slash delimited
PATTERN      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

Open in new window

. Dot delimited
PATTERN      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

Open in new window

- Dash delimited
PATTERN      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

Open in new window

Space delimited
PATTERN      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

Open in new window

Time Only
PATTERN      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

Open in new window

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

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

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

Open in new window

13
Comment
Author:PortletPaul
  • 3
4 Comments
LVL 67

Expert Comment

by:Jim Horn
This is damn handy.  Voted Yes.
1
LVL 67

Expert Comment

by:Jim Horn
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

2
LVL 34

Expert Comment

by:Mike Eghtebas
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
1
LVL 67

Expert Comment

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

Featured Post

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Learn More!

Join & Write a Comment

SQL - Inserting Data
Video by: Zia
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
How to Bring Back the SQL Database from Suspect Mode to Normal Mode?
Video by: Bharat
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month