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

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

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

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

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

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

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

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

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

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

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

Examples related to sql-server

Examples related to tsql