How to convert a datetime to string in T-SQL
Sql ServerTsqlSql Server Problem Overview
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.)
Sql Server Solutions
Solution 1 - Sql Server
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)
Solution 2 - Sql Server
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:
Solution 3 - Sql Server
There are 3 different methods depending on what I is my requirement and which version I am using.
Here are the methods..
-
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'
-
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'
-
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'
Solution 4 - Sql Server
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
Solution 5 - Sql Server
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.
Solution 6 - 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
Solution 7 - Sql Server
Check CAST and CONVERT syntax of t-sql:
Solution 8 - Sql Server
Try below :
DECLARE @myDateTime DATETIME
SET @myDateTime = '2013-02-02'
-- Convert to string now
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
Solution 9 - Sql Server
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'
Solution 10 - Sql Server
In the stored procedure for me works something like this.
convert(varchar(10), StartingDate) AS 'StartingDate'