SQL Server date formats

Source: Internet
Author: User
Tags month name

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 completed MED 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 yyyy1
Hh: miam (or pm)
Default Select convert (varchar (20), getdate (), 100) Jan 1 2005 pm1
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 YY1 - Select convert (varchar (9), getdate (), 6) as [DD mon YY] 04 Jul 061
Dd mon yyyy1 - Select convert (varchar (11), getdate (), 106) as [DD mon YYYY] 04 Jul 20061
Mon DD, YY1 - Select convert (varchar (10), getdate (), 7) as [Mon DD, YY] Jan 24, 981
Mon DD, yyyy1 - Select convert (varchar (12), getdate (), 107) as [Mon DD, yyyy] Jan 24,199 81
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: 253pm1
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 (24 h)1 Europe default + milliseconds Select convert (varchar (24), getdate (), 113) 28 Apr 2006 00: 34: 55: 1901
Hh: MI: SS: Mmm (24 h) - Select convert (varchar (12), getdate (), 114) as [hh: MI: SS: Mmm (24 h)] 11: 34: 23: 013
YYYY-MM-DD hh: MI: SS (24 h) ODBC canonical Select convert (varchar (19), getdate (), 120) 13:42:24
YYYY-MM-DD hh: MI: Ss. Mmm (24 hours) 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: mmmam1 Kuwaiti Select convert (varchar (26), getdate (), 130) 28 Apr 2006 12: 39: 32: 429am1
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 ofConvertFunction.

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] 2017-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, yyyy1 Select datename (mm, getdate () + right (convert (varchar (12), getdate (), 107), 9) as [month DD, yyyy] July 04,200 61
Mon yyyy1 Select substring (convert (varchar (11), getdate (), 113), 4, 8) as [Mon YYYY] Apr 20061
Month yyyy1 Select datename (mm, getdate () + ''+ Cast (Year (getdate () as varchar (4) as [month YYYY] February 20061
Dd month1 Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate () as [DD month] 11 September1
Month dd1 Select datename (mm, getdate () + ''+ Cast (Day (getdate () as varchar (2) as [month DD] September 111
Dd month yy1 Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate ()) + ''+ right (cast (Year (getdate () as varchar (4), 2) as [DD month yy] 19 February 721
Dd month yyyy1 Select cast (Day (getdate () as varchar (2) + ''+ datename (mm, getdate () +'' + Cast (Year (getdate ()) as varchar (4) as [DD month YYYY] 11 September 20021
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-yy1 Select Replace (right (convert (varchar (9), getdate (), 6), 6), '', '-') as [Mon-yy] Sep-021
Mon-yyyy1 Select Replace (right (convert (varchar (11), getdate (), 106), 8), '', '-') as [Mon-yyyy] Sep-20021
DD-mon-yy1 Select Replace (convert (varchar (9), getdate (), 6), '', '-') as [DD-mon-yy] 25-dec-051
DD-mon-yyyy1 Select Replace (convert (varchar (11), getdate (), 106), '', '-') as [DD-mon-yyyy] 25-dec-20051

1 To make the month name in upper case, simply use the upper string function.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.