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

User · Answer

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'

User · Answer

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  amp  date in SQL Server

User · Answer

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

User · Answer

Try below    DECLARE  myDateTime DATETIME SET  myDateTime    2013-02-02   -- Convert to string now SELECT LEFT CONVERT VARCHAR   myDateTime  120   10

User · Answer

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

User · Answer

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

User · Answer

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

User · 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      SQLFiddle Demo SQL Server Date Formats

User · Answer

Check CAST and CONVERT syntax of t-sql   http   msdn microsoft com en-us library ms187928 aspx

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

Examples related to sql-server

Examples related to tsql