Convert DateTime Format

17/05/2009

תגיות: , ,
5 תגובות

 

Hi ,

Recently I have encountered the need for extended date format such as quarter.

So I found many tips over the web and concentrated them into to one post.

For example 'MM/YYYY', and select Quarter from Getdate()…

Hope this help!

Ronen

 

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

 

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]

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

99-01-24

YYYY-MM-DD

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

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') 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.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

5 תגובות

  1. Prajeesh27/05/2009 ב 15:32

    Very much helpful.

    הגב
  2. ToratordDrale25/02/2011 ב 17:19

    35images|AHAHAHAHAHA I LOVE YOU GUYS<3 Any video with that amazing wig and accentsп»ї is just brilliant.
    http://selectworld.net/?p=137

    thplousaqq

    הגב