Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Performance Monitoring in the Cloud

Monitor and Troubleshoot SQL Server Performance

5 ways monitoring SQL Server Agent Jobs can make your job easier

Deploying Applications in Azure without Configuration Limitations

Date and Time Conversions Using SQL Server

MSSQLTips author Edgewood Solutions By:   |   Read Comments (19)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Dates

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 to format a date/time string. One of the first considerations is the actual date/time 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 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. Below is a list of formats and an example of the output:

Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

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

Sample statement Output
select replace(convert(varchar, getdate(),101),'/','') 12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') 12302006004426
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
  • Check out all of the SQL Server tip categories on the site

Last Update: 1/3/2007

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

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Monday, January 05, 2015 - 3:20:07 PM - Sharim Read The Tip

--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 05, 2014 - 1:40:28 AM - San Read The Tip

Hi Greg Robidoux,

Thanks a lot.

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

Once again thanks....

Saturday, November 01, 2014 - 6:54:03 AM - Greg Robidoux Read The Tip

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 01, 2014 - 6:42:45 AM - San Read The Tip

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 - giam Read The Tip
thank you very much.

Friday, November 30, 2012 - 9:42:22 AM - Jeremy Kadlec Read The Tip


Have you checked out Tim Ford's tip - http://www.mssqltips.com/sqlservertip/1712/sql-server-function-to-convert-integer-date-to-datetime-format/?

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 - satheeshkumar Read The Tip



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 Kadlec Read The Tip


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 - Dev Read The Tip

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 - e Read The Tip

Monday, September 17, 2012 - 6:10:13 PM - Mike Read The Tip

Don't forget 23:   2006-12-30

Friday, August 17, 2012 - 12:57:12 AM - tintu Read The Tip

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 Tiwari Read The Tip

its very very helpful...

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

Wednesday, May 16, 2012 - 9:23:58 AM - Jeremy Kadlec Read The Tip


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 - Tessa Read The Tip

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 Vyas Read The Tip

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

Thursday, January 08, 2009 - 6:46:58 AM - tosscrosby Read The Tip

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 08, 2009 - 6:27:17 AM - Senthilkumar.S Read The Tip

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 07, 2009 - 12:50:38 PM - tosscrosby Read The Tip

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.

Sponsor Information