How to get different date formats in SQL Server

 How to get different date formats in SQL Server

1. Use the SELECT statement with CONVERT function and date format option for the date values needed
2. To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
3. To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
4. Check out the chart to get a list of all format options

Below is a list of SQL date formats and an example of the output.  The date used for all of these examples is "2006-12-30 00:38:54.840".

DATE ONLY FORMATS
Format #	Query	Format	Sample
1	select convert(varchar, getdate(), 1)	mm/dd/yy	12/30/06
2	select convert(varchar, getdate(), 2)	yy.mm.dd	06.12.30
3	select convert(varchar, getdate(), 3)	dd/mm/yy	30/12/06
4	select convert(varchar, getdate(), 4)	dd.mm.yy	30.12.06
5	select convert(varchar, getdate(), 5)	dd-mm-yy	30-12-06
6	select convert(varchar, getdate(), 6)	dd-Mon-yy	30 Dec 06
7	select convert(varchar, getdate(), 7)	Mon dd, yy	Dec 30, 06
10	select convert(varchar, getdate(), 10)	mm-dd-yy	12-30-06
11	select convert(varchar, getdate(), 11)	yy/mm/dd	06/12/30
12	select convert(varchar, getdate(), 12)	yymmdd	061230
23	select convert(varchar, getdate(), 23)	yyyy-mm-dd	2006-12-30
101	select convert(varchar, getdate(), 101)	mm/dd/yyyy	12/30/2006
102	select convert(varchar, getdate(), 102)	yyyy.mm.dd	2006.12.30
103	select convert(varchar, getdate(), 103)	dd/mm/yyyy	30/12/2006
104	select convert(varchar, getdate(), 104)	dd.mm.yyyy	30.12.2006
105	select convert(varchar, getdate(), 105)	dd-mm-yyyy	30-12-2006
106	select convert(varchar, getdate(), 106)	dd Mon yyyy	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Mon dd, yyyy	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	mm-dd-yyyy	12-30-2006
111	select convert(varchar, getdate(), 111)	yyyy/mm/dd	2006/12/30
112	select convert(varchar, getdate(), 112)	yyyymmdd	20061230
TIME ONLY FORMATS
8	select convert(varchar, getdate(), 8)	hh:mm:ss	00:38:54
14	select convert(varchar, getdate(), 14)	hh:mm:ss:nnn	00:38:54:840
24	select convert(varchar, getdate(), 24)	hh:mm:ss	00:38:54
108	select convert(varchar, getdate(), 108)	hh:mm:ss	00:38:54
114	select convert(varchar, getdate(), 114)	hh:mm:ss:nnn	00:38:54:840
DATE & TIME FORMATS
0	select convert(varchar, getdate(), 0)	Mon dd yyyy hh:mm AM/PM	Dec 30 2006 12:38AM
9	select convert(varchar, getdate(), 9)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2006 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	dd Mon yyyy hh:mm:ss:nnn AM/PM	30 Dec 2006 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	yyyy-mm-dd hh:mm:ss	2006-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
22	select convert(varchar, getdate(), 22)	mm/dd/yy hh:mm:ss AM/PM	12/30/06 12:38:54 AM
25	select convert(varchar, getdate(), 25)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
100	select convert(varchar, getdate(), 100)	Mon dd yyyy hh:mm AM/PM	Dec 30 2006 12:38AM
109	select convert(varchar, getdate(), 109)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2006 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	dd Mon yyyy hh:mm:ss:nnn	30 Dec 2006 00:38:54:840
120	select convert(varchar, getdate(), 120)	yyyy-mm-dd hh:mm:ss	2006-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	yyyy-mm-dd hh:mm:ss:nnn	2006-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	yyyy-mm-dd T hh:mm:ss:nnn	2006-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	yyyy-mm-dd T hh:mm:ss:nnn	2006-12-30T00:38:54.840
ISLAMIC CALENDAR DATES
130	select convert(nvarchar, getdate(), 130)	dd mmm yyyy hh:mi:ss:nnn AM/PM
131	select convert(nvarchar, getdate(), 131)	dd mmm yyyy hh:mi:ss:nnn AM/PM	10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Format	Output
select replace(convert(varchar, getdate(),101),'/','')	mmddyyyy	12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	mmddyyyyhhmmss	12302006004426