SQL Server Convert Datetime to String + Examples

In this SQL Server tutorial, we will learn How to convert Datetime datatype to String in SQL Server, Different SQL Server convert DateTime to string examples, and cover the below topic.

  • SQL Server Convert Datetime to String Format
  • SQL Server Convert Datetime column to String
  • SQL Server Convert Datetime to String yyyymmddhhmmss
  • SQL Server Convert Datetime to String yyyy-mm-dd
  • SQL Server Convert Datetime to String with millisecond
  • SQL Server Convert Datetime to String dd/mm/yyyy
  • SQL Server Convert Datetime to String custom format
  • SQL Server Convert Datetime to String with time zone
  • SQL Server Convert Datetime to String mm/dd/yyyy
  • SQL Server Convert Datetime to time string
Converting Datetime datatype to Sting
SQL Server Convert Datetime to String

Here, I have used sql server management studio and sql server 2019.

SQL Server Convert Datetime to String Format

There are mainly 2 functions available in SQL Server 2019/2017, used to convert a DateTime data type to string. And in this section, we’re going to discuss both the function with the help of examples.

Cast() Function

The Cast() function in SQL Server, is used to convert a variable of one data type to another or data from one data type to another. The Cast() function has the following syntax.

CAST ( [Expression] AS Datatype)
  • The expression in the above syntax can be any valid expression, variable, table column, etc.
  • The Datatype in the syntax is the target data type that we want as a result.

Example

DECLARE @date_time DATETIME
SET @date_time = '2021-06-21 10:54:25.543'
SELECT  CAST(@date_time AS varchar(20)) AS 'New String datatype' 

In the example above, we have declared a DATETIME variable then we have provided a value for the variable. Lastly, we use the CAST() function within the SELECT statement to convert the DateTime variable to varchar(20).

The query will return the follwong result.

Converting Datetime to Sting using Cast
Converting Datetime to Sting using Cast()

Convert() Function

The Convert() is another function available in SQL Server used to convert data from one data type to another data type. But the Convert() function is more efficient than the Cast() function because it has 3 parameters instead of just 2. It has the following syntax.

CONVERT(data_type(length), expression, style)

The style is an optional parameter, used to define format for the resulting expression. Without stype parameter Convert() and Cas() function will return same output.

Example

DECLARE @date_time DATETIME
SET @date_time = '2021-06-21 10:54:25.543'
SELECT  CONVERT(varchar(20),@date_time,113) AS 'New String datatype' 

We have declared a DATETIME variable in the example then we have provided a value for the variable. Lastly, we use the CONVERT() function within the SELECT statement to convert the DateTime variable to varchar(20). And we have also defined the style parameter as 113, which will return the result in “dd Mon yyyy hh:mm:ss” format.

After successful execution, the query will return the following result.

Converting Datetime to Sting using Convert
Converting Datetime to Sting using Convert()

Read: How to execute function in SQL with parameters

SQL Server Convert Datetime column to String

Now there can scenarios where we have a DateTime column in our table, but we want only date or time values out of it. For this, we can convert a DateTime data type to a string using the Convert() function, and by specifying the styles parameter, we can easily format the result as we want.

Let’s understand this implementation with the help of an example. Now for demonstration, consider the following employee table.

Sample table to convert datetime to string
Sample Employee table

The above table consists of a DateTime column named Joining, which carries the joining date-time details of an employee. Now we will use the following query to convert the DateTime column to a string data type representing a date.

SELECT Joining, 
       Convert(varchar(11),Joining,106) As 'Joining date' 
From Sample

So in the above query, we are using the Convert() function within The SELECT statement to convert the DateTime column to varchar. And for the date format, we have defined the style parameter as 106. In the end, it will return the following output.

Converting datetime column to string
Converting Datetime column to String

Read: SQL Server Replace Function

SQL Server Convert Datetime to String yyyymmddhhmmss

In SQL Server we cannot directly convert a DateTime expression to a string data type having “yyyymmddhhmmss” format. For this purpose, a standard approach can be as follows.

  • First, use the SQL Server Convert() function to change the DateTime expression to yyyymmdd string format.
  • After this, use another Convert() function to get the hh:mm:ss string from the DateTime value
  • After this, use the Replace() function to replace colon (:) with an empty string in hh:mm:ss string.
  • In the end, concatenate the results of the first Convert() function with the result of Replace() function to get the desired output.

Now for demonstration, we are going to use the previous employee table and implementing the following query.

SELECT [Joining], 
       CONVERT(VARCHAR, [Joining], 112) + 
       REPLACE(CONVERT(VARCHAR, [Joining], 108), ':', '') AS [yyyymmddhhmmss]
From Sample

So in the above query, we are using the approach mentioned before on a column named Joining, containing DateTime values. And after execution, it will return the following result.

Converting datetime to yyyymmddhhmmss string
Final Result

SQL Server Convert Datetime to String yyyy-mm-dd

In SQL Server, we can easily convert a DateTime data type to a string having “yyyy-mm-dd” format by using the Convert() function. For this, we can follow the following syntax.

CONVERT(varchar(10), datetime_expression, 126)

In Convert() function, we have to specify style code as 126, which is used for the “yyyy-mm-dd” format.

Now for demonstration, we are going to use the previous employee table and implementing the following query.

SELECT [Joining], 
        CONVERT(varchar(10), [Joining], 126) AS [yyyy-mm-dd]
From Sample

So in the above query, we are applying the Convert() function on a column named Joining, containing the joining detail of each employee as a DateTime expression. And after execution, it will return the following result.

Converting datetime to yyyy-mm-dd string
Converting Datetime to yyyy-mm-dd String

Read: SQL Server Convert String to Date + Examples

SQL Server Convert Datetime to String dd/mm/yyyy

Now to convert a DateTime data type to a string having “dd/mm/yyyy” format in SQL Server 2019, we can use the Convert() function, with the following syntax.

CONVERT(varchar(10), datetime_expression, 103)

In Convert() function, we have to use the style parameter and define the style code as 103, which is used for the “dd/mm/yyyy” format.

Now for demonstration, we are going to use the previous employee table and implementing the following query.

SELECT [Joining], 
        CONVERT(varchar(10), [Joining], 103) AS [dd/mm/yyyy]
From Sample

After succesful execution, the query will return the following output.

Converting datetime to a specific string
Converting Datetime to dd/mm/yyyy String

SQL Server Convert Datetime to String with millisecond

In SQL Server, we can use the Convert() function to convert Datetime to a string with milliseconds. And by using the style parameter in Convert() function, we can even convert a string to different formats having milliseconds.

Consider the following table, having multiple string formats with millisecond.

NOTE:- The GETDATE() function returns the current system Datetime value.

Style CodeFormatQueryResult
9Mon dd yyyy hh:mm:ss:nnn AM/PMSELECT CONVERT(VARCHAR, GETDATE(), 9)Jun 21 2021 3:31:16:513PM
13dd Mon yyyy hh:mm:ss:nnn (24h)SELECT CONVERT(VARCHAR, GETDATE(), 13)21 Jun 2021 15:31:16:513
21yyyy-mm-dd hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 21)2021-06-21 15:31:16.513
113dd Mon yyyy hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 113)21 Jun 2021 15:39:14:667
121yyyy-mm-dd hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 121)2021-06-21 15:39:14.667
126yyyy-mm-dd T hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 126)2021-06-21T15:39:14.667
127yyyy-mm-ddThh:mi:ss.mmmZSELECT CONVERT(VARCHAR, GETDATE(), 127)2021-06-21T15:39:14.667

Read: How to create functions in SQL Server Management Studio

SQL Server Convert Datetime to String mm/dd/yyyy

In SQL Server 2019, we can easily convert the DateTime data type to a string in “mm/dd/yyyy” format by using the following syntax.

CONVERT(VARCHAR(10), datetime_expression, 101)

Let’s understand this conversion with the help of an example. And for demonstration, we will take the previous sample employee table and try to convert the DateTime column to mm/dd/yyyy format.

SELECT [Joining], 
        CONVERT(varchar(10), [Joining], 101) AS [mm/dd/yyyy]
From Sample

So in the above query, we are using the Covert() function within the SELECT statement to convert the DateTime column to varchar. And we have also defined the style parameter in Convert() function as ‘101‘, which is used for “mm/dd/yyyy” format.

After execution the query, will return the following result.

Converting datetime to string using convert function
Converting Datetime to mm/dd/yyyy String

SQL Server Convert Datetime to String custom format

In SQL Server 2019, we can use the Convert() function to convert an expression of Datetime data type to string. The Convert() is an efficient function, which also allows formatting the resulting expression by using styles parameter. The style parameter is an optional parameter that accepts integer values.

Now there are various formatting options available in the Convert() function while converting a Datetime expression to string. Each format has its own style code that we can use to format the resulting string.

Here is a list of formats that we can use while converting datetime to a string.

Without century (yy)With century (yyyy)FormatStandard
0 or 100mon dd yyyy hh:miAM (or PM)Default
11011 = mm/dd/yy
101 = mm/dd/yyyy
U.S.
21022 = yy.mm.dd
102 = yyyy.mm.dd
ANSI
31033 = dd/mm/yy
103 = dd/mm/yyyy
British/French
41044 = dd.mm.yy
104 = dd.mm.yyyy
German
51055 = dd-mm-yy
105 = dd-mm-yyyy
Italian
61066 = dd mon yy
106 = dd mon yyyy
71077 = Mon dd, yy
107 = Mon dd, yyyy
8108hh:mi:ss
9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM)Default + milliseconds
1011010 = mm-dd-yy
110 = mm-dd-yyyy

USA
1111111 = yy/mm/dd
111 = yyyy/mm/dd
JAPAN
1211212 = yymmdd
112 = yyyymmdd
ISO
13 or 113dd mon yyyy hh:mi:ss:mmm(24h)Europe default + milliseconds
14114hh:mi:ss:mmm(24h)
20 or 120yyyy-mm-dd hh:mi:ss(24h)ODBC canonical
21 0r 121yyyy-mm-dd hh:mi:ss.mmm(24h)ODBC canonical (with milliseconds)
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)ISO8601
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)ISO8601 with time zone Z.
130dd mon yyyy hh:mi:ss:mmmAMHijri
131dd/mm/yyyy hh:mi:ss:mmmAMHijri

Example

DECLARE @date_time DATETIME
SET @date_time = '2021-06-21 10:54:25.543'
SELECT  CONVERT(varchar(20),@date_time,113) AS 'String-1',
        CONVERT(varchar(20),@date_time,126) AS 'String-2'

In the above query, first, we have defined a DateTime variable with a value. After this, we are using the Convert() function to convert that DateTime variable to 2 different string formats by using the style parameter. And the query will return the following output.

Converting datetime column to different string format
Query Result

Read: SQL Server Convert Datetime to date + Examples

SQL Server Convert Datetime to time string

Now to convert a Datetime expression to a string in time format, we can use the Convert() function. In Convert() function, we have to specify the style code to format the string in hh:mm:ss form.

Here is a table containing all the various style codes for the time format with query and query results.

Note:- The GETDATE() function returns the current system Datetime value.

Style CodeFormatQueryResult
8hh:mm:ssSELECT CONVERT(VARCHAR, GETDATE(), 8)17:48:25
14hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 14)17:48:25:633
24hh:mm:ssSELECT CONVERT(VARCHAR, GETDATE(), 24)17:48:25
108hh:mm:ssSELECT CONVERT(VARCHAR, GETDATE(), 108)17:48:25
114hh:mm:ss:nnnSELECT CONVERT(VARCHAR, GETDATE(), 114)17:48:25:633

Example

Now, for example, we are taking the previous employee table having a joining date column containing the Datetime expression. And we will try to convert the DateTime column to a time string using the following query.

SELECT [Joining], 
        CONVERT(varchar(10), [Joining], 108) AS [hh:mm:ss]
From Sample

In the query, we are using the Convert() function within the SELECT statement to convert the Joining column to hh:mm:ss string format. And, we have also defined the style parameter as 108. In the end, the query will return the following result.

Converting datetime to time string
Converting Datetime to hh:mm:ss string

SQL Server Convert Datetime to String with time zone

In SQL Server, we cannot directly convert a DateTime data type to a string with a time zone. For this implementation, we have to define the time zone value at the time of conversion. And to specify the time zone we have to specify the AT TIME ZONE clause.

Let’s understand the implementation with the help of an example. And for demonstration, we are taking the previous employee table having a joining date column as a Datetime expression. And we will try to convert the Datetime to a varchar field having a time zone value.

SELECT [Joining], 
       CONVERT(varchar,[Joining] AT TIME ZONE 'US Eastern Standard Time')
       AS 'String With TimeZone'
FROM Sample

In the above example, we are using the Convert() function within the SELECT statement. In the Convert() function, we have defined the DateTime expression as Joining, which is a DateTime column in the Sample table. And with the column, we have defined the AT TIME ZONE clause using which we have defined the time zone.

In the end, the query will return the following output.

Converting datetime to string with timezone in sql server
Query Result

You may like the following sql server articles:

So in this SQL Server tutorial, we have learned How to convert Datetime datatype to String, Different converting examples, and cover the below topic.

  • SQL Server Convert Datetime to String Format
  • SQL Server Convert Datetime column to String
  • SQL Server Convert Datetime to String yyyymmddhhmmss
  • SQL Server Convert Datetime to String yyyy-mm-dd
  • SQL Server Convert Datetime to String with millisecond
  • SQL Server Convert Datetime to String dd/mm/yyyy
  • SQL Server Convert Datetime to String custom format
  • SQL Server Convert Datetime to String with time zone
  • SQL Server Convert Datetime to String mm/dd/yyyy
  • SQL Server Convert Datetime to time string