Date and Time Conversions Using SQL Server

By: MSSQLTips   |   Updated: 2022-12-08   |   Comments (61)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Dates

Problem

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution

SQL Server provides a number of options you can use for formatting a date/time string in SQL queries and stored procedures either from an input file (Excel, CSV, etc.) or a date column (datetime, datetime2, smalldatetime, etc.) from a table. One of the first considerations is the actual date/time value needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time (UTC) is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed.

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 "2022-12-30 00:38:54.840".

DATE ONLY FORMATS
Format #	Query	Format	Sample
1	select convert(varchar, getdate(), 1)	mm/dd/yy	12/30/22
2	select convert(varchar, getdate(), 2)	yy.mm.dd	22.12.30
3	select convert(varchar, getdate(), 3)	dd/mm/yy	30/12/22
4	select convert(varchar, getdate(), 4)	dd.mm.yy	30.12.22
5	select convert(varchar, getdate(), 5)	dd-mm-yy	30-12-22
6	select convert(varchar, getdate(), 6)	dd-Mon-yy	30 Dec 22
7	select convert(varchar, getdate(), 7)	Mon dd, yy	Dec 30, 22
10	select convert(varchar, getdate(), 10)	mm-dd-yy	12-30-22
11	select convert(varchar, getdate(), 11)	yy/mm/dd	22/12/30
12	select convert(varchar, getdate(), 12)	yymmdd	221230
23	select convert(varchar, getdate(), 23)	yyyy-mm-dd	2022-12-30
31	select convert(varchar, getdate(), 31)	yyyy-dd-mm	2022-30-12
32	select convert(varchar, getdate(), 32)	mm-dd-yyyy	12-30-2022
33	select convert(varchar, getdate(), 33)	mm-yyyy-dd	12-2022-30
34	select convert(varchar, getdate(), 34)	dd-mm-yyyy	30-12-2022
35	select convert(varchar, getdate(), 35)	dd-yyyy-mm	30-2022-12
101	select convert(varchar, getdate(), 101)	mm/dd/yyyy	12/30/2022
102	select convert(varchar, getdate(), 102)	yyyy.mm.dd	2022.12.30
103	select convert(varchar, getdate(), 103)	dd/mm/yyyy	30/12/2022
104	select convert(varchar, getdate(), 104)	dd.mm.yyyy	30.12.2022
105	select convert(varchar, getdate(), 105)	dd-mm-yyyy	30-12-2022
106	select convert(varchar, getdate(), 106)	dd Mon yyyy	30 Dec 2022
107	select convert(varchar, getdate(), 107)	Mon dd, yyyy	Dec 30, 2022
110	select convert(varchar, getdate(), 110)	mm-dd-yyyy	12-30-2022
111	select convert(varchar, getdate(), 111)	yyyy/mm/dd	2022/12/30
112	select convert(varchar, getdate(), 112)	yyyymmdd	20221230
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 2022 12:38AM
9	select convert(varchar, getdate(), 9)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2022 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	dd Mon yyyy hh:mm:ss:nnn AM/PM	30 Dec 2022 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	yyyy-mm-dd hh:mm:ss	2022-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
22	select convert(varchar, getdate(), 22)	mm/dd/yy hh:mm:ss AM/PM	12/30/22 12:38:54 AM
25	select convert(varchar, getdate(), 25)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
26	select convert(varchar, getdate(), 26)	yyyy-dd-mm hh:mm:ss:nnn	2022-30-12 00:38:54.840
27	select convert(varchar, getdate(), 27)	mm-dd-yyyy hh:mm:ss:nnn	12-30-2022 00:38:54.840
28	select convert(varchar, getdate(), 28)	mm-yyyy-dd hh:mm:ss:nnn	12-2022-30 00:38:54.840
29	select convert(varchar, getdate(), 29)	dd-mm-yyyy hh:mm:ss:nnn	30-12-2022 00:38:54.840
30	select convert(varchar, getdate(), 30)	dd-yyyy-mm hh:mm:ss:nnn	30-2022-12 00:38:54.840
100	select convert(varchar, getdate(), 100)	Mon dd yyyy hh:mm AM/PM	Dec 30 2022 12:38AM
109	select convert(varchar, getdate(), 109)	Mon dd yyyy hh:mm:ss:nnn AM/PM	Dec 30 2022 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	dd Mon yyyy hh:mm:ss:nnn	30 Dec 2022 00:38:54:840
120	select convert(varchar, getdate(), 120)	yyyy-mm-dd hh:mm:ss	2022-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	yyyy-mm-dd hh:mm:ss:nnn	2022-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	yyyy-mm-dd T hh:mm:ss:nnn	2022-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	yyyy-mm-dd T hh:mm:ss:nnn	2022-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/1444 12:38:54:840AM

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

If you want to get a list of all valid date and time formats, you could use the code below and change the @date to GETDATE() or any other date you want to use.  This will output just the valid formats.

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2006-12-30 00:38:54.840'

CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormats

Recommended Reading

Continue your learning on Microsoft SQL Server dates with these tips and tutorials:

• Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
• SQL Server Date and Time Data Types
• SQL Server function to convert integer date to datetime format
• SQL Database DateTime Best Practices
• Format SQL Server Dates with FORMAT Function
• SQL Server Date Functions
• Add and Subtract Dates using DATEADD in SQL Server

Next Steps

• The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available.
• These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently.
• Also, check out the SQL Server FORMAT Function to Format Dates.

About the author

MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

View all my tips