Date and Time Conversions Using SQL Server

By:   |   Updated: 2021-04-22   |   Comments (57)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Dates

Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

Let us help you stay informed and learn something new each day. Click here to keep informed.

Thank you,
Greg Robidoux and Jeremy Kadlec ( Co-Founders)

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.


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

Format #QueryFormatSample
1 select convert(varchar, getdate(), 1) mm/dd/yy12/30/06
2 select convert(varchar, getdate(), 2)
3 select convert(varchar, getdate(), 3) dd/mm/yy30/12/06
4 select convert(varchar, getdate(), 4)
5 select convert(varchar, getdate(), 5) dd-mm-yy30-12-06
6 select convert(varchar, getdate(), 6) dd-Mon-yy30 Dec 06
7 select convert(varchar, getdate(), 7) Mon dd, yyDec 30, 06
10 select convert(varchar, getdate(), 10) mm-dd-yy12-30-06
11 select convert(varchar, getdate(), 11) yy/mm/dd06/12/30
12 select convert(varchar, getdate(), 12) yymmdd061230
23 select convert(varchar, getdate(), 23) yyyy-mm-dd2006-12-30
101 select convert(varchar, getdate(), 101) mm/dd/yyyy12/30/2006
102 select convert(varchar, getdate(), 102)
103 select convert(varchar, getdate(), 103) dd/mm/yyyy30/12/2006
104 select convert(varchar, getdate(), 104)
105 select convert(varchar, getdate(), 105) dd-mm-yyyy30-12-2006
106 select convert(varchar, getdate(), 106) dd Mon yyyy30 Dec 2006
107 select convert(varchar, getdate(), 107) Mon dd, yyyyDec 30, 2006
110 select convert(varchar, getdate(), 110) mm-dd-yyyy12-30-2006
111 select convert(varchar, getdate(), 111) yyyy/mm/dd2006/12/30
112 select convert(varchar, getdate(), 112) yyyymmdd20061230
8 select convert(varchar, getdate(), 8) hh:mm:ss00:38:54
14select convert(varchar, getdate(), 14) hh:mm:ss:nnn00:38:54:840
24select convert(varchar, getdate(), 24) hh:mm:ss00:38:54
108 select convert(varchar, getdate(), 108) hh:mm:ss00:38:54
114select convert(varchar, getdate(), 114) hh:mm:ss:nnn00:38:54:840
0 select convert(varchar, getdate(), 0) Mon dd yyyy hh:mm AM/PMDec 30 2006 12:38AM
9select convert(varchar, getdate(), 9) Mon dd yyyy hh:mm:ss:nnn AM/PMDec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) dd Mon yyyy hh:mm:ss:nnn AM/PM30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) yyyy-mm-dd hh:mm:ss2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) yyyy-mm-dd hh:mm:ss:nnn2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) mm/dd/yy hh:mm:ss AM/PM12/30/06 12:38:54 AM
25select convert(varchar, getdate(), 25) yyyy-mm-dd hh:mm:ss:nnn2006-12-30 00:38:54.840
100select convert(varchar, getdate(), 100) Mon dd yyyy hh:mm AM/PMDec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Mon dd yyyy hh:mm:ss:nnn AM/PMDec 30 2006 12:38:54:840AM
113select convert(varchar, getdate(), 113) dd Mon yyyy hh:mm:ss:nnn30 Dec 2006 00:38:54:840
120select convert(varchar, getdate(), 120) yyyy-mm-dd hh:mm:ss2006-12-30 00:38:54
121select convert(varchar, getdate(), 121) yyyy-mm-dd hh:mm:ss:nnn2006-12-30 00:38:54.840
126select convert(varchar, getdate(), 126) yyyy-mm-dd T hh:mm:ss:nnn2006-12-30T00:38:54.840
127select convert(varchar, getdate(), 127) yyyy-mm-dd T hh:mm:ss:nnn2006-12-30T00:38:54.840
130select convert(nvarchar, getdate(), 130) dd mmm yyyy hh:mi:ss:nnn AM/PM
131select convert(nvarchar, getdate(), 131) dd mmm yyyy hh:mi:ss:nnn AM/PM10/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 statementFormatOutput
select replace(convert(varchar, getdate(),101),'/','') mmddyyyy12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') mmddyyyyhhmmss12302006004426

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 )
 INSERT INTO #dateFormats
 SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
 SET @counter = @counter + 1
 SET @counter = @counter + 1
 IF @counter >= 150
 SELECT * FROM #dateFormats

Recommended Reading

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

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.

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

About the author
Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of

View all my tips

Article Last Updated: 2021-04-22

Comments For This Article

Tuesday, September 13, 2022 - 8:28:48 AM - Greg RobidouxBack To Top(90470)
Hi Osman,

You could just do UPDATE statements to convert your text values to the standard time values you mentioned. Another option is to use a CASE statement to make the change if you don't want to change the actual data.


Monday, September 12, 2022 - 6:29:56 PM - OsmanBack To Top(90467)

Please help me with this..

I got a really messy data that I am trying to clean. There is a column there that has time. Most of the time values are entered in this format 18h00, which is fine. But there are rows that have “morning” or “afternoon” or “after dark” instead of actual time.

My question is: how do I convert these strings like “afternoon” which I know could be like “13h00” or something to a time value instead of having it as a string.

Tuesday, August 2, 2022 - 7:32:08 PM - LeLikBack To Top(90331)
you have the typo for Format #13

Wednesday, April 27, 2022 - 12:27:58 AM - negahBack To Top(90039)
hi i convert '2015-04-14 00:30:00.000' to '2015-04-14 24:30:00.000' in sql

(my parametr type as datetime)

please help me

Tuesday, December 14, 2021 - 11:16:06 AM - Greg RobidouxBack To Top(89582)
Hi Krishna,

try this.

declare @d int = 44542 -- Dec 14, 2021
select CONVERT(varchar(25),cast(@d as datetime),105)


Tuesday, December 14, 2021 - 7:43:18 AM - KrishnaBack To Top(89580)

I want to convert a Text Field which is saving the Data in String into a Date field. The value stored is in the below format.

Value Stored = 66028
Actual Value (or) Desired Output = 10-11-2021 (dd-mm-yyyy)


Monday, September 20, 2021 - 10:35:42 AM - Greg RobidouxBack To Top(89246)
Hi Amol,

you could create another table with the mapping and then join to that table based on the day of the month to return your A, B, C, etc. values.


Saturday, September 18, 2021 - 1:32:31 PM - AmolBack To Top(89240)
i am beginner in sql
i have one task of conversion of date like.
if day of month is 01 then conversion would like A, like for 02 it would be B( 01= A,02=B,.......27=AA,28=AB...)

could we do like code this sql please suggest solution if it could be done.


Monday, March 1, 2021 - 10:59:59 AM - Greg RobidouxBack To Top(88317)
Thanks Ron. This has been updated.


Sunday, February 28, 2021 - 11:54:18 AM - Ron MosesBack To Top(88314)
Example #3 st the top of the article is wrong. You indicate that YYYY will be returned by format value 1. But this format only returns YY, as you indicate in the chart immediately below. 101 returns the year with century.

Friday, April 3, 2020 - 9:43:20 AM - Jeff ModenBack To Top(85264)

Be advised that the FORMAT function is horrible for performance.  It takes a very typical 43 times longer than even some of the more complex things you can do with CONVERT.  I STRONGLY RECOMMEND AVOIDING THE USE OF THE FORMAT FUNCTION FOR THAT REASON.  It's the "death of a thousand small cuts".

Tuesday, March 17, 2020 - 3:42:44 PM - Greg RobidouxBack To Top(85131)

Hi George, you can do this with the format function as well.  Here is a tip about that:

Also, I have heard that the format function is a lot slower than the convert function.


Tuesday, March 17, 2020 - 3:13:22 PM - GeorgeBack To Top(85130)

Wouldn't most of this be moot with the FORMAT function?  That will automatically turn it into an NVARCHAR(4000) field, and you have a lot of flexibility to define it how you want.

    format(getdate(), 'yyyy-MM-dd hh:mm:ss') AS [today],
    format(getdate(), 'yyyy-MM-dd')          AS [today2]

Wednesday, January 15, 2020 - 3:40:50 AM - DambaBack To Top(83774)


Monday, December 16, 2019 - 9:54:34 AM - Greg RobidouxBack To Top(83436)

Thanks Gregg for the feedback.  We will see if we can update all of the examples.

The reader could also run the T-SQL script and enter other dates.  This will produce all valid results, so they can see how the date would be formatted.


Saturday, December 14, 2019 - 11:27:03 AM - Gregg TractonBack To Top(83426)

revise your example date, please.  use a example date (ex, 1/2/1994) that has a month AND day less than 10 so readers can see if the leading zero is prefixed or not.

Tuesday, July 30, 2019 - 5:42:29 AM - MANISHBack To Top(81898)


Wednesday, July 17, 2019 - 9:45:22 AM - jackBack To Top(81778)

Thank for the providing that giant table formats!

Monday, June 3, 2019 - 11:55:28 AM - Anne CaoBack To Top(81310)

Thank you for the good tip.

I also see sometimes the column could store seconds only. The seconds is the seconds since 0 hours of the day.

Any conversion formula?


Thursday, May 16, 2019 - 11:00:26 AM - Greg RobidouxBack To Top(80105)

Thanks Stewart for the input.

As you mentioned this tip uses VARCHAR and that could be changed to NVARCHAR which will return the same results for most of the items and take care of format 130.


Thursday, May 16, 2019 - 10:49:18 AM - StewartBack To Top(80104)

Formats 130 and 131 are the Islamic calendar date.  But 130 contains Arabic characters, and therefore you would need to convert to nvarchar, not varchar.  Still, the order of the parts comes out in a bit of a muddle, unless I tell my software to render it right-to-left.

Thursday, April 18, 2019 - 4:57:37 PM - Greg RobidouxBack To Top(79597)

Hi Ben,

you can do this: 

SELECT CONVERT(datetime,'1/1/2018 12:00:00 AM')



Thursday, April 18, 2019 - 3:19:51 PM - Ben LaRocheBack To Top(79596)

Does anyone know how to convert this format "1/1/2018 12:00:00 AM" (currently in text) to a date time type?



Friday, April 12, 2019 - 6:03:09 AM - Sindhuja K SBack To Top(79542)

Its very nice article .

Its saved my time.

Thank you.

Wednesday, March 20, 2019 - 12:36:07 PM - Greg RobidouxBack To Top(79351)

Hi Zulfiqar, it looks like the output is 2019-03-20.

You could also just do this and get the same result.  The third parameter doesn't seem to make a difference.



Wednesday, March 20, 2019 - 12:13:12 PM - Zulfiqar DholkawalaBack To Top(79350)

Hi What would this output?


Thank you.

Wednesday, March 20, 2019 - 10:25:54 AM - marcusBack To Top(79348)

i love you guys

Monday, March 11, 2019 - 2:04:56 PM - Greg RobidouxBack To Top(79258)

Hi David,

Take a look at this tip:


Monday, March 11, 2019 - 7:31:30 AM - DavidBack To Top(79245)


Thank you for this great post,but i can't find this date format 

Jeu. 07 Mar 2019

 any suggestion please ?

Thank you

Thursday, March 22, 2018 - 2:14:35 PM - AubreyBack To Top(75502)


 A simpler way to format the date is:  



This will produce a date in this format: 2018-03-22

You can replace the “GETDATE()” with the name of date column you want to modify.


As for the Time, use:

SELECT FORMAT(GETDATE(), ‘hh:mm’) AS ‘ColumnName’

This will produce the time in this format: 01:07

Or, if you want the time returned in Military format, change the ‘hh’ to upper case ‘HH’


This will produce the time in this format: 13:09

Hope this helps someone.


Thursday, March 22, 2018 - 6:23:48 AM - RihanBack To Top(75500)

This was helpful


Saturday, October 21, 2017 - 7:27:09 AM - Zumer JanBack To Top(68613)



Excellent Post

Tuesday, May 9, 2017 - 11:26:34 AM - Julie Back To Top(55621)

 I would add that cast(date_expression as date) is sometimes a useful solution. It keeps the date characteristics for sorting, comparing, etc. but drops the time portion. This is great for items going to excel.


Thursday, April 6, 2017 - 2:11:44 PM - Greg RobidouxBack To Top(54369)

Hi Koduru,

Take a look at this tip:


Thursday, April 6, 2017 - 1:01:01 PM - koduru jaladakshiBack To Top(54364)


 hi how to get result in sql server like




please help me

thank u.


Thursday, December 10, 2015 - 2:16:54 AM - Adarsh v nairBack To Top(40228)

01/01/0100 10:00:00 how  to convert time 10:00:0

Saturday, October 3, 2015 - 5:22:09 AM - senyaBack To Top(38820)

hello,i just want to create a date from the table in sql server 2008.But there one error was araised whatever change the datatype(varchar,int,char).such as that error is,

"Arithmetic overflow error converting expression to data type int.
The statement has been terminated".

how ll clear this error.

Monday, August 31, 2015 - 10:25:38 AM - UmitBack To Top(38572)

Hi there,

I am retrieving data with SQL from a Oracle database where I encounter different date formats in the same data group.

I run the SQL query in VBA and the query results are pasted in an excel tab. So in the same data column I have data with multiple date formats in excel.

Only one date format recognized as date, according to pc's regional settings. I need to convert all data into same format before I retrieve them to excel.

right now I use this: TO_CHAR(v.BASLANGICTARIHI, 'DD/MM/YYYY') but it does not help.

Can someone help me here?



Monday, January 5, 2015 - 3:20:07 PM - SharimBack To Top(35831)

--Output as char with space like 3 1 1 0 2 0 1 4.
declare @dt varchar(120)
declare @hold1 varchar(2)
declare @hold2 varchar(15)
declare @i int

set @dt = CONVERT(varchar(26),getdate(),103)
set @dt = REPLACE(@dt,'/','')
set @i=1
set @hold2=''

WHILE (@i < len(@dt)+1)
  set @hold1 = substring(@dt,@i, 1)+' '
  set @hold2 += @hold1
  set @i += 1
select @hold2

Wednesday, November 5, 2014 - 1:40:28 AM - SanBack To Top(35184)

Hi Greg Robidoux,

Thanks a lot.

Your suggestion were working well... Really you have given me a great thing.

Once again thanks....

Saturday, November 1, 2014 - 6:54:03 AM - Greg RobidouxBack To Top(35146)

Hi San,

use this select replace(convert(varchar, getdate(),103),'/','')  to get the output you need and then use one of these functions to add the space between each number

Saturday, November 1, 2014 - 6:42:45 AM - SanBack To Top(35145)

Input date (31/10/2014)

Output as char with space like 3 1 1 0 2 0 1 4.


Can anyone help me?


This is for cheque date printing...



Monday, February 18, 2013 - 11:32:21 PM - giamBack To Top(22251)
thank you very much.

Friday, November 30, 2012 - 9:42:22 AM - Jeremy KadlecBack To Top(20670)


Have you checked out Tim Ford's tip -

I believe he has a function to take care of the date logic, but I think you will have to modify it to include the time logic you need.


Thank you,
Jeremy Kadlec

Friday, November 30, 2012 - 2:40:01 AM - satheeshkumarBack To Top(20656)



Input - 20121130121020 = output 2012/11/30 12:10:20


Can any one help me out this


Monday, November 19, 2012 - 9:37:04 AM - Jeremy KadlecBack To Top(20409)


Can you post the date format you are seeing in SSMS and the final format you would like?

Thank you,
Jeremy Kadlec

Monday, November 19, 2012 - 5:59:40 AM - DevBack To Top(20405)

How to convert datetime format stored data to 24hr time format in SSMS 2008?
Any idea on this?


Wednesday, September 19, 2012 - 1:06:03 AM - eBack To Top(19559)

Monday, September 17, 2012 - 6:10:13 PM - MikeBack To Top(19540)

Don't forget 23:   2006-12-30

Friday, August 17, 2012 - 12:57:12 AM - tintuBack To Top(19089)

i want extracting date from sql server,using php how to get this?? i used


echo $d;

 this is not working

Tuesday, July 24, 2012 - 5:47:14 AM - Gayatri TiwariBack To Top(18766)

its very very helpful...

still i need few more details... for few more formulaes....

Wednesday, May 16, 2012 - 9:23:58 AM - Jeremy KadlecBack To Top(17482)


Would this work for you?

convert(varchar,getdate(),105) + ' ' + convert(varchar,getdate(),108)

Is there any reason you would not use this format:

9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM


Have you also seen these tips:


Thank you,
Jeremy Kadlec

Wednesday, May 16, 2012 - 7:42:46 AM - TessaBack To Top(17479)

I would like to see the complete date and time a DD-MM-YYYY HH:MM:SS

I use: convert(varchar,getdate(),105) + convert(varchar,getdate(),108)

I get: 16-05-201213:42:18


How would I get the extra space between the Date and Time to get 16-05-2012 13:42:18

Thursday, May 12, 2011 - 11:15:21 AM - Vijay Prakash VyasBack To Top(13814)

It's very helpful info thanks for help!!!!!

Thursday, January 8, 2009 - 6:46:58 AM - tosscrosbyBack To Top(2533)

Actually, I was simply complimenting the "tip" as it provided exactly what I needed for ANY date:

 select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

This converts any MSSQL datetime to MMDDYYYYHHMMSS - exactly what the Oracle folks wanted!


Thanks anyway :-)

Thursday, January 8, 2009 - 6:27:17 AM - Senthilkumar.SBack To Top(2529)

Select the particular year,month, day,pls do following query


select * from barrowBooks where year(barrow_date)='2008' and month(barrow_date)='11' and day(barrow_date)='12'  order by barrow_date desc


convert the date and time  using this query


select convert(char(11),getdate(),108) -->Result of time  11:18:18

select convert(char(10),getdate(),101) -->Result of Date  11/06/2008

Wednesday, January 7, 2009 - 12:50:38 PM - tosscrosbyBack To Top(2520)

I just had a request from our Oracle team to see if I could supply them with dates in a MMDDYYYYHHMMSS format. Came here and found what I need in all of about 30 seconds. Thanks.