Advertisement
Advertisement


How to convert a datetime to string in T-SQL


Question

I'm surprised not to be able to find this question here already.

I have a date time var and I want to convert it to a string so that I can append it to another string. I want it in a format that can be converted easily back to a date time.

How can I do this?

(I want the date part and the time part.)

2013/02/22
1
89
2/22/2013 5:24:34 PM

Accepted Answer

The following query will get the current datetime and convert into string. with the following format
yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar(25), getdate(), 120) 
2013/02/22
154
2/22/2013 5:25:38 PM

There are many different ways to convert a datetime to a string. Here is one way:

SELECT convert(varchar(25), getdate(), 121)  – yyyy-mm-dd hh:mm:ss.mmm

See Demo

Here is a website that has a list of all of the conversions:

How to Format datetime & date in SQL Server

2013/02/22

You can use the convert statement in Microsoft SQL Server to convert a date to a string. An example of the syntax used would be:

SELECT convert(varchar(20), getdate(), 120)

The above would return the current date and time in a string with the format of YYYY-MM-DD HH:MM:SS in 24 hour clock.

You can change the number at the end of the statement to one of many which will change the returned strings format. A list of these codes can be found on the MSDN in the CAST and CONVERT reference section.

2013/02/22

There are 3 different methods depending on what I is my requirement and which version I am using.

Here are the methods..

1) Using Convert

DECLARE @DateTime DATETIME = GETDATE();
--Using Convert
SELECT
    CONVERT(NVARCHAR, @DateTime,120) AS 'myDateTime'
    ,CONVERT(NVARCHAR(10), @DateTime, 120) AS 'myDate'
    ,RIGHT(CONVERT(NVARCHAR, @DateTime, 120),8) AS 'myTime'

2) Using Cast (SQL Server 2008 and beyond)

SELECT
    CAST(@DateTime AS DATETIME2) AS 'myDateTime'
    ,CAST(@DateTime AS DATETIME2(3)) AS 'myDateTimeWithPrecision'
    ,CAST(@DateTime AS DATE) AS 'myDate'
    ,CAST(@DateTime AS TIME) AS 'myTime'
    ,CAST(@DateTime AS TIME(3)) AS 'myTimeWithPrecision'

3) Using Fixed-length character data type

DECLARE @myDateTime NVARCHAR(20) = CONVERT(NVARCHAR, @DateTime, 120);
DECLARE @myDate NVARCHAR(10) = CONVERT(NVARCHAR, @DateTime, 120);

SELECT
    @myDateTime AS 'myDateTime'
    ,@myDate AS 'myDate'
2014/02/20

In addition to the CAST and CONVERT functions in the previous answers, if you are using SQL Server 2012 and above you use the FORMAT function to convert a DATETIME based type to a string.

To convert back, use the opposite PARSE or TRYPARSE functions.

The formatting styles are based on .NET (similar to the string formatting options of the ToString() method) and has the advantage of being culture aware. eg.

DECLARE @DateTime DATETIME2 = SYSDATETIME();
DECLARE @StringResult1 NVARCHAR(100) = FORMAT(@DateTime, 'g') --without culture
DECLARE @StringResult2 NVARCHAR(100) = FORMAT(@DateTime, 'g', 'en-gb') 
SELECT @DateTime
SELECT @StringResult1, @StringResult2
SELECT PARSE(@StringResult1 AS DATETIME2)
SELECT PARSE(@StringResult2 AS DATETIME2 USING 'en-gb')

Results:

2015-06-17 06:20:09.1320951
6/17/2015 6:20 AM
17/06/2015 06:20
2015-06-17 06:20:00.0000000
2015-06-17 06:20:00.0000000
2015/06/17

Check CAST and CONVERT syntax of t-sql:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

2013/02/22