RSS

SQL Server Date Formats

27 Oct

Calendar

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats

Date Format

SQL Statement

Sample Output

Mon DD YYYY

HH:MIAM (or PM)

SELECT CONVERT(VARCHAR(20), GETDATE(),
100)

Jan
1 2005 1:29PM

MM/DD/YY

SELECT CONVERT(VARCHAR(8), GETDATE(),
1) AS [MM/DD/YY]


11/23/98

MM/DD/YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(),
101) AS [MM/DD/YYYY]


11/23/1998


YY.MM.DD

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

72.01.01


YYYY.MM.DD

SELECT CONVERT(VARCHAR(10), GETDATE(),
102) AS [YYYY.MM.DD]


1972.01.01

DD/MM/YY

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]


19/02/72

DD/MM/YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(),
103) AS [DD/MM/YYYY]


19/02/1972

DD.MM.YY

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

25.12.05


DD.MM.YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(),
104) AS [DD.MM.YYYY]

25.12.2005

DD-MM-YY

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]

24-01-98

DD-MM-YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(),
105) AS [DD-MM-YYYY]

24-01-1998

DD Mon YY

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

04 Jul 06

DD Mon YYYY

SELECT CONVERT(VARCHAR(11), GETDATE(),
106) AS [DD MON YYYY]

04 Jul 2006

Mon DD, YY

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]

Jan 24, 98

Mon DD, YYYY

SELECT CONVERT(VARCHAR(12), GETDATE(),
107) AS [Mon DD, YYYY]

Jan 24,
1998


HH:MM:SS

SELECT CONVERT(VARCHAR(8), GETDATE(),
108)

03:24:53

Mon DD YYYY HH:MI:SS:MMMAM (or PM)

SELECT CONVERT(VARCHAR(26), GETDATE(),
109)

Apr 28 2006
12:32:29:253PM

MM-DD-YY

SELECT CONVERT(VARCHAR(8), GETDATE(),
10) AS [MM-DD-YY]

01-01-06

MM-DD-YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(),
110) AS [MM-DD-YYYY]

01-01-2006


YY/MM/DD

SELECT CONVERT(VARCHAR(8), GETDATE(),
11) AS [YY/MM/DD]

98/11/23


YYYY/MM/DD

SELECT CONVERT(VARCHAR(10), GETDATE(),
111) AS [YYYY/MM/DD]


1998/11/23


YYMMDD

SELECT CONVERT(VARCHAR(6), GETDATE(),
12) AS [YYMMDD]

980124


YYYYMMDD

SELECT CONVERT(VARCHAR(8), GETDATE(),
112) AS [YYYYMMDD]


19980124

DD Mon YYYY HH:MM:SS:MMM(24h)

SELECT CONVERT(VARCHAR(24), GETDATE(),
113)

28 Apr 2006 00:34:55:190

HH:MI:SS:MMM(24H)

SELECT CONVERT(VARCHAR(12), GETDATE(),
114) AS [HH:MI:SS:MMM(24H)]


11:34:23:013


YYYY-MM-DD HH:MI:SS(24h)

SELECT CONVERT(VARCHAR(19), GETDATE(),
120)


1972-01-01 13:42:24


YYYY-MM-DD HH:MI:SS.MMM(24h)

SELECT CONVERT(VARCHAR(23), GETDATE(),
121)


1972-02-19 06:35:24.489


YYYY-MM-DDTHH:MM:SS:MMM

SELECT CONVERT(VARCHAR(23), GETDATE(),
126)


1998-11-23T11:25:43:250

DD Mon YYYY HH:MI:SS:MMMAM

SELECT CONVERT(VARCHAR(26), GETDATE(),
130)


23 ذو الحجة 1434
1:25:04:

DD/MM/YYYY HH:MI:SS:MMMAM

SELECT CONVERT(VARCHAR(25), GETDATE(),
131)


23/12/1434 1:25:35:363PM

You can see more details at: CAST and CONVERT (Transact-SQL)

 
1 Comment

Posted by on October 27, 2013 in MS SQL Server

 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

One response to “SQL Server Date Formats

  1. Medicine For Genital Herpes

    June 14, 2014 at 6:36 AM

    Woah! I’m really enjoying the template/theme of this site.
    It’s simple, yet effective. A lot of times it’s challenging to get that “perfect balance”
    between superb usability and visual appearance. I must say you’ve done a
    superb job with this. Also, the blog loads very fast
    for me on Firefox. Exceptional Blog!

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: