Thursday, September 18, 2008

Date Formats in SQL Server

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

Standard

SQL Statement

Sample Output

Mon DD YYYY 1
HH:MIAM (or PM)

Default

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

Jan 1 2005 1:29PM 1

MM/DD/YY

USA

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

11/23/98

MM/DD/YYYY

USA

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

11/23/1998

YY.MM.DD

ANSI

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

72.01.01

YYYY.MM.DD

ANSI

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

1972.01.01

DD/MM/YY

British/French

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

19/02/72

DD/MM/YYYY

British/French

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

19/02/1972

DD.MM.YY

German

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

25.12.05

DD.MM.YYYY

German

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

25.12.2005

DD-MM-YY

Italian

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

24-01-98

DD-MM-YYYY

Italian

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

24-01-1998

DD Mon YY 1

-

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

04 Jul 06 1

DD Mon YYYY 1

-

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

04 Jul 2006 1

Mon DD, YY 1

-

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

Jan 24, 98 1

Mon DD, YYYY 1

-

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

Jan 24, 1998 1

HH:MM:SS

-

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

03:24:53

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

Default +
milliseconds

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

Apr 28 2006 12:32:29:253PM 1

MM-DD-YY

USA

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

01-01-06

MM-DD-YYYY

USA

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

ISO

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

980124

YYYYMMDD

ISO

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

19980124

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

Europe default + milliseconds

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

28 Apr 2006 00:34:55:190 1

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)

ODBC Canonical

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

1972-01-01 13:42:24

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

ODBC Canonical
(with milliseconds)

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

1972-02-19 06:35:24.489

YYYY-MM-DDTHH:MM:SS:MMM

ISO8601

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

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

DD Mon YYYY HH:MI:SS:MMMAM 1

Kuwaiti

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

28 Apr 2006 12:39:32:429AM 1

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

Kuwaiti

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

28/04/2006 12:39:32:429AM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats

Date Format

SQL Statement

Sample Output

YY-MM-DD

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

99-01-24

YYYY-MM-DD

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

1999-01-24

MM/YY

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]

08/99

MM/YYYY

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

12/2005

YY/MM

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

99/08

YYYY/MM

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

2005/12

Month DD, YYYY 1

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

July 04, 2006 1

Mon YYYY 1

SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]

Apr 2006 1

Month YYYY 1

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]

February 2006 1

DD Month 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]

11 September 1

Month DD 1

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]

September 11 1

DD Month YY 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]

19 February 72 1

DD Month YYYY 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]

11 September 2002 1

MM-YY

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]

12/92

MM-YYYY

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

05-2006

YY-MM

SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]

92/12

YYYY-MM

SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]

2006-05

MMDDYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]

122506

MMDDYYYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]

12252006

DDMMYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]

240702

DDMMYYYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]

24072002

Mon-YY 1

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]

Sep-02 1

Mon-YYYY 1

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]

Sep-2002 1

DD-Mon-YY 1

SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]

25-Dec-05 1

DD-Mon-YYYY 1

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]

25-Dec-2005 1
1 To make the month name in upper case, simply use the UPPER string function.
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)

bdnews24.com | Bangla Version

Loading...

The Daily Star

Loading...