Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Date and Time Conversions Using SQL Server


By:   |   Read Comments (28)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 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 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.

How to get different SQL Server date formats

  1. Use the date format option along with CONVERT function
  2. To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
  3. To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)
  4. Check out the chart to get a list of all format options

Below is a list of 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 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
12 select convert(varchar, getdate(), 12) 061230
23 select convert(varchar, getdate(), 23) 2006-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
112 select convert(varchar, getdate(), 112) 20061230
TIME ONLY FORMATS
8 select convert(varchar, getdate(), 8) 00:38:54
14 select convert(varchar, getdate(), 14) 00:38:54:840
24 select convert(varchar, getdate(), 24) 00:38:54
108 select convert(varchar, getdate(), 108) 00:38:54
114 select convert(varchar, getdate(), 114) 00:38:54:840
DATE & TIME FORMATS
0 select convert(varchar, getdate(), 0) Dec 12 2006 12:38AM
9 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) 30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) 2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) 2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) 12/30/06 12:38:54 AM
25 select convert(varchar, getdate(), 25) 2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840
FORMATS WITH ISSUES
130 select convert(varchar, getdate(), 130) 10 ?? ????? 1427 12:38:54:840A
131 select convert(varchar, getdate(), 131) 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 Output
select replace(convert(varchar, getdate(),101),'/','') 12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') 12302006004426

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 varchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(varchar, @counter), CONVERT(varchar,@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
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.


Last Update:

First Published: 2007-01-03


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, March 22, 2018 - 2:14:35 PM - Aubrey Back To Top

 

 A simpler way to format the date is:  

 

SELECT CAST(GETDATE() AS DATE) AS ‘ColumnName’

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’

SELECT FORMAT(GETDATE(), ‘HH:mm’) AS ‘Time’

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

Hope this helps someone.

 


Thursday, March 22, 2018 - 6:23:48 AM - Rihan Back To Top

This was helpful

 


Saturday, October 21, 2017 - 7:27:09 AM - Zumer Jan Back To Top

 

 

Excellent Post


Tuesday, May 09, 2017 - 11:26:34 AM - Julie Back To Top

 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 06, 2017 - 2:11:44 PM - Greg Robidoux Back To Top

Hi Koduru,

Take a look at this tip: https://www.mssqltips.com/sqlservertip/2507/determine-sql-server-date-and-time-with-datepart-and-datename-functions/

 


Thursday, April 06, 2017 - 1:01:01 PM - koduru jaladakshi Back To Top

 

 hi how to get result in sql server like

year:2017

date:06

month:04

please help me

thank u.

 


Thursday, December 10, 2015 - 2:16:54 AM - Adarsh v nair Back To Top

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


Saturday, October 03, 2015 - 5:22:09 AM - senya Back To Top

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 - Umit Back To Top

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?

Thanks.

 


Monday, January 05, 2015 - 3:20:07 PM - Sharim Back To Top

--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)
BEGIN
  set @hold1 = substring(@dt,@i, 1)+' '
  set @hold2 += @hold1
  set @i += 1
END
select @hold2


Wednesday, November 05, 2014 - 1:40:28 AM - San Back To Top

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 Back To Top

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

http://dbamentalist.wordpress.com/2013/06/17/t-sql-function-add-a-space-between-all-characters/comment-page-1/

http://www.codeproject.com/Tips/426728/T-SQL-Function-Add-a-space-between-all-characters


Saturday, November 01, 2014 - 6:42:45 AM - San Back To Top

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 Back To Top
thank you very much.

Friday, November 30, 2012 - 9:42:22 AM - Jeremy Kadlec Back To Top

satheeshkumar,

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.

HTH.

Thank you,
Jeremy Kadlec


Friday, November 30, 2012 - 2:40:01 AM - satheeshkumar Back To Top

hI

BELOW MY REQUORMENT

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 Back To Top

Dev,

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 Back To Top

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 Back To Top


Monday, September 17, 2012 - 6:10:13 PM - Mike Back To Top

Don't forget 23:   2006-12-30


Friday, August 17, 2012 - 12:57:12 AM - tintu Back To Top

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

$d=convert(varchar,getdate(),101);

echo $d;

 this is not working


Tuesday, July 24, 2012 - 5:47:14 AM - Gayatri Tiwari Back To Top

its very very helpful...

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


Wednesday, May 16, 2012 - 9:23:58 AM - Jeremy Kadlec Back To Top

Tessa,

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:

http://www.mssqltips.com/sqlservertip/2588/new-date-and-time-functions-in-sql-server-2012/

http://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/

HTH.

Thank you,
Jeremy Kadlec


Wednesday, May 16, 2012 - 7:42:46 AM - Tessa Back To Top

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 Back To Top

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


Thursday, January 08, 2009 - 6:46:58 AM - tosscrosby Back To Top

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 Back To Top

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 Back To Top

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.


Learn more about SQL Server tools