Date and Time Conversions Using SQL Server
By: MSSQLTips | Updated: 2022-12-08 | Comments (61) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 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
- Use the SELECT statement with CONVERT function and date format option for the date values needed
- To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
- To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
- 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:
Sample statement | Format | Output |
---|---|---|
select replace(convert(varchar, getdate(),101),'/','') | mmddyyyy | 12302022 |
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') | mmddyyyyhhmmss | 12302022004426 |
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

View all my tips
Article Last Updated: 2022-12-08