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
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.
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.
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.
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 SampleSo 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.
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 SampleSo 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.
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 SampleSo 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.
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 SampleAfter succesful execution, the query will return the following output.
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 Code | Format | Query | Result |
|---|---|---|---|
| 9 | Mon dd yyyy hh:mm:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 9) | Jun 21 2021 3:31:16:513PM |
| 13 | dd Mon yyyy hh:mm:ss:nnn (24h) | SELECT CONVERT(VARCHAR, GETDATE(), 13) | 21 Jun 2021 15:31:16:513 |
| 21 | yyyy-mm-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 21) | 2021-06-21 15:31:16.513 |
| 113 | dd Mon yyyy hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 113) | 21 Jun 2021 15:39:14:667 |
| 121 | yyyy-mm-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 121) | 2021-06-21 15:39:14.667 |
| 126 | yyyy-mm-dd T hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 126) | 2021-06-21T15:39:14.667 |
| 127 | yyyy-mm-ddThh:mi:ss.mmmZ | SELECT 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 SampleSo 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.
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) | Format | Standard |
|---|---|---|---|
| – | 0 or 100 | mon dd yyyy hh:miAM (or PM) | Default |
| 1 | 101 | 1 = mm/dd/yy 101 = mm/dd/yyyy | U.S. |
| 2 | 102 | 2 = yy.mm.dd 102 = yyyy.mm.dd | ANSI |
| 3 | 103 | 3 = dd/mm/yy 103 = dd/mm/yyyy | British/French |
| 4 | 104 | 4 = dd.mm.yy 104 = dd.mm.yyyy | German |
| 5 | 105 | 5 = dd-mm-yy 105 = dd-mm-yyyy | Italian |
| 6 | 106 | 6 = dd mon yy 106 = dd mon yyyy | – |
| 7 | 107 | 7 = Mon dd, yy 107 = Mon dd, yyyy | – |
| 8 | 108 | hh:mi:ss | – |
| – | 9 or 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) | Default + milliseconds |
| 10 | 110 | 10 = mm-dd-yy 110 = mm-dd-yyyy | USA |
| 11 | 111 | 11 = yy/mm/dd 111 = yyyy/mm/dd | JAPAN |
| 12 | 112 | 12 = yymmdd 112 = yyyymmdd | ISO |
| – | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24h) | Europe default + milliseconds |
| 14 | 114 | hh:mi:ss:mmm(24h) | – |
| – | 20 or 120 | yyyy-mm-dd hh:mi:ss(24h) | ODBC canonical |
| – | 21 0r 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) | ODBC canonical (with milliseconds) |
| – | 126 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) | ISO8601 |
| – | 127 | yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) | ISO8601 with time zone Z. |
| – | 130 | dd mon yyyy hh:mi:ss:mmmAM | Hijri |
| – | 131 | dd/mm/yyyy hh:mi:ss:mmmAM | Hijri |
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.
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 Code | Format | Query | Result |
|---|---|---|---|
| 8 | hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 8) | 17:48:25 |
| 14 | hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 14) | 17:48:25:633 |
| 24 | hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 24) | 17:48:25 |
| 108 | hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 108) | 17:48:25 |
| 114 | hh:mm:ss:nnn | SELECT 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 SampleIn 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.
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 SampleIn 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.
You may like the following sql server articles:
- SQL Server Convert Function + Examples
- SQL Server Substring Function
- SQL Server DateTime vs Datetime2
- Conversion failed when converting date in SQL Server
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.