[sql-server] How to convert a datetime to string in T-SQL

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.)

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.

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

SELECT CONVERT(varchar, @datetime, 103) --for UK Date format 'DD/MM/YYYY'

101 - US - MM/DD/YYYY

108 - Time - HH:MI:SS

112 - Date - YYYYMMDD

121 - ODBC - YYYY-MM-DD HH:MI:SS.FFF

20 - ODBC - YYYY-MM-DD HH:MI:SS

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'

Check CAST and CONVERT syntax of t-sql:

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

Try below :

DECLARE @myDateTime DATETIME
SET @myDateTime = '2013-02-02'

-- Convert to string now
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

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

This has been answered by a lot of people, but I feel like the simplest solution has been left out.

SQL SERVER (I believe its 2012+) has implicit string equivalents for DATETIME2 as shown here

Look at the section on "Supported string literal formats for datetime2"

To answer the OPs question explicitly:

DECLARE @myVar NCHAR(32)
DECLARE @myDt DATETIME2
SELECT @myVar = @GETDATE()
SELECT @myDt = @myVar
PRINT(@myVar)
PRINT(@myDt)

output:

Jan 23 2019 12:24PM             
2019-01-23 12:24:00.0000000

Note: The first variable (myVar) is actually holding the value '2019-01-23 12:24:00.0000000' as well. It just gets formatted to Jan 23 2019 12:24PM due to default formatting set for SQL SERVER that gets called on when you use PRINT. Don't get tripped up here by that, the actual string in (myVer) = '2019-01-23 12:24:00.0000000'