Convert DATETIME to VARCHAR in T-SQL: Explore 53 Different Format Styles with 1 Single Script

In today's post, we will learn how to convert DateTime to Text in T-SQL and demonstrate all 53 different formatting styles such as yyyy-MM-dd, yyyyMMddHHmmss with a 1 single script.

You can use this Fiddle to follow along and practice DATETIME to VARCHAR Format convertion

Convert DATETIME to VARCHAR yyyy-MM-dd

The query selects the current date and time (GETDATE()) and also converts it to the "yyyy-MM-dd" format using the style code 120.

SELECT GETDATE() AS CurrentDateTime,
       CONVERT(VARCHAR(10), GETDATE(), 120) AS [yyyy-MM-dd]

CurrentDateTime	yyyy-MM-dd
2023-08-18 17:22:14.403	2023-08-18

Convert DATETIME to VARCHAR yyyyMMdd

The query retrieves the current date and time (GETDATE()) and then converts it into the "yyyyMMdd" format using the style code 112. This format represents the year, month, and day in a concatenated string.

SELECT GETDATE() AS CurrentDateTime,
       CONVERT(VARCHAR, GETDATE(), 112) AS yyyyMMdd

CurrentDateTime	yyyyMMdd
2023-08-18 17:22:14.403	20230818

Convert DATETIME to VARCHAR yyyyMMddHHmmss

This query obtains the current date and time (GETDATE()) and then transforms it into a string without colons, dashes, or spaces. The result is formatted as "yyyyMMddHHmmss", capturing the year, month, day, hour, minute, and second components in a single sequence.

SELECT GETDATE() AS CurrentDateTime,
       REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') AS yyyyMMddHHmmss

CurrentDateTime	yyyyMMddHHmmss
2023-08-18 17:22:14.407	20230818172214

T-SQL to Convert DATETIME to NVARCHAR Format Styles

Dynamically generate formatted datetime values using a WHILE loop in SQL Server

DECLARE @counter INT = 0;
DECLARE @current_datetime DATETIME = GETDATE();

DROP TABLE

IF EXISTS #FormatDateDemo
	CREATE TABLE #FormatDateDemo (
		StyleNumber INT
		,StyleOutput NVARCHAR(40)
		,Query NVARCHAR(100)
		);

WHILE (@counter <= 131)
BEGIN
	BEGIN TRY
		INSERT INTO #FormatDateDemo
		SELECT @counter AS StyleNumber
			,CONVERT(NVARCHAR, @current_datetime, @counter) AS StyleOutput
			,CONCAT (
				'SELECT CONVERT(NVARCHAR, GETDATE(), '
				,CONVERT(NVARCHAR, @counter)
				,')'
				) AS Query;

		SET @counter = @counter + 1;
	END TRY

	BEGIN CATCH
		SET @counter = @counter + 1;
	END CATCH
END

SELECT @current_datetime AS CurrentDateTime
	,*
FROM #FormatDateDemo;

tip

Note that NVARCHAR is only required for style number 130.

SELECT CONVERT(NVARCHAR, GETDATE(), 130)

For other style numbers, you can simply use VARCHAR.

SELECT CONVERT(VARCHAR, GETDATE(), 23)

CurrentDateTime	StyleNumber	StyleOutput	Query
2023-08-18 17:16:35.587	0	Aug 18 2023 5:16PM	SELECT CONVERT(NVARCHAR, GETDATE(), 0)
2023-08-18 17:16:35.587	1	08/18/23	SELECT CONVERT(NVARCHAR, GETDATE(), 1)
2023-08-18 17:16:35.587	2	23.08.18	SELECT CONVERT(NVARCHAR, GETDATE(), 2)
2023-08-18 17:16:35.587	3	18/08/23	SELECT CONVERT(NVARCHAR, GETDATE(), 3)
2023-08-18 17:16:35.587	4	18.08.23	SELECT CONVERT(NVARCHAR, GETDATE(), 4)
2023-08-18 17:16:35.587	5	18-08-23	SELECT CONVERT(NVARCHAR, GETDATE(), 5)
2023-08-18 17:16:35.587	6	18 Aug 23	SELECT CONVERT(NVARCHAR, GETDATE(), 6)
2023-08-18 17:16:35.587	7	Aug 18, 23	SELECT CONVERT(NVARCHAR, GETDATE(), 7)
2023-08-18 17:16:35.587	8	17:16:35	SELECT CONVERT(NVARCHAR, GETDATE(), 8)
2023-08-18 17:16:35.587	9	Aug 18 2023 5:16:35:587PM	SELECT CONVERT(NVARCHAR, GETDATE(), 9)
2023-08-18 17:16:35.587	10	08-18-23	SELECT CONVERT(NVARCHAR, GETDATE(), 10)
2023-08-18 17:16:35.587	11	23/08/18	SELECT CONVERT(NVARCHAR, GETDATE(), 11)
2023-08-18 17:16:35.587	12	230818	SELECT CONVERT(NVARCHAR, GETDATE(), 12)
2023-08-18 17:16:35.587	13	18 Aug 2023 17:16:35:587	SELECT CONVERT(NVARCHAR, GETDATE(), 13)
2023-08-18 17:16:35.587	14	17:16:35:587	SELECT CONVERT(NVARCHAR, GETDATE(), 14)
2023-08-18 17:16:35.587	20	2023-08-18 17:16:35	SELECT CONVERT(NVARCHAR, GETDATE(), 20)
2023-08-18 17:16:35.587	21	2023-08-18 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 21)
2023-08-18 17:16:35.587	22	08/18/23 5:16:35 PM	SELECT CONVERT(NVARCHAR, GETDATE(), 22)
2023-08-18 17:16:35.587	23	2023-08-18	SELECT CONVERT(NVARCHAR, GETDATE(), 23)
2023-08-18 17:16:35.587	24	17:16:35	SELECT CONVERT(NVARCHAR, GETDATE(), 24)
2023-08-18 17:16:35.587	25	2023-08-18 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 25)
2023-08-18 17:16:35.587	26	2023-18-08 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 26)
2023-08-18 17:16:35.587	27	08-18-2023 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 27)
2023-08-18 17:16:35.587	28	08-2023-18 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 28)
2023-08-18 17:16:35.587	29	18-08-2023 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 29)
2023-08-18 17:16:35.587	30	18-2023-08 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 30)
2023-08-18 17:16:35.587	31	2023-18-08	SELECT CONVERT(NVARCHAR, GETDATE(), 31)
2023-08-18 17:16:35.587	32	08-18-2023	SELECT CONVERT(NVARCHAR, GETDATE(), 32)
2023-08-18 17:16:35.587	33	08-2023-18	SELECT CONVERT(NVARCHAR, GETDATE(), 33)
2023-08-18 17:16:35.587	34	18-08-2023	SELECT CONVERT(NVARCHAR, GETDATE(), 34)
2023-08-18 17:16:35.587	35	18-2023-08	SELECT CONVERT(NVARCHAR, GETDATE(), 35)
2023-08-18 17:16:35.587	100	Aug 18 2023 5:16PM	SELECT CONVERT(NVARCHAR, GETDATE(), 100)
2023-08-18 17:16:35.587	101	08/18/2023	SELECT CONVERT(NVARCHAR, GETDATE(), 101)
2023-08-18 17:16:35.587	102	2023.08.18	SELECT CONVERT(NVARCHAR, GETDATE(), 102)
2023-08-18 17:16:35.587	103	18/08/2023	SELECT CONVERT(NVARCHAR, GETDATE(), 103)
2023-08-18 17:16:35.587	104	18.08.2023	SELECT CONVERT(NVARCHAR, GETDATE(), 104)
2023-08-18 17:16:35.587	105	18-08-2023	SELECT CONVERT(NVARCHAR, GETDATE(), 105)
2023-08-18 17:16:35.587	106	18 Aug 2023	SELECT CONVERT(NVARCHAR, GETDATE(), 106)
2023-08-18 17:16:35.587	107	Aug 18, 2023	SELECT CONVERT(NVARCHAR, GETDATE(), 107)
2023-08-18 17:16:35.587	108	17:16:35	SELECT CONVERT(NVARCHAR, GETDATE(), 108)
2023-08-18 17:16:35.587	109	Aug 18 2023 5:16:35:587PM	SELECT CONVERT(NVARCHAR, GETDATE(), 109)
2023-08-18 17:16:35.587	110	08-18-2023	SELECT CONVERT(NVARCHAR, GETDATE(), 110)
2023-08-18 17:16:35.587	111	2023/08/18	SELECT CONVERT(NVARCHAR, GETDATE(), 111)
2023-08-18 17:16:35.587	112	20230818	SELECT CONVERT(NVARCHAR, GETDATE(), 112)
2023-08-18 17:16:35.587	113	18 Aug 2023 17:16:35:587	SELECT CONVERT(NVARCHAR, GETDATE(), 113)
2023-08-18 17:16:35.587	114	17:16:35:587	SELECT CONVERT(NVARCHAR, GETDATE(), 114)
2023-08-18 17:16:35.587	120	2023-08-18 17:16:35	SELECT CONVERT(NVARCHAR, GETDATE(), 120)
2023-08-18 17:16:35.587	121	2023-08-18 17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 121)
2023-08-18 17:16:35.587	126	2023-08-18T17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 126)
2023-08-18 17:16:35.587	127	2023-08-18T17:16:35.587	SELECT CONVERT(NVARCHAR, GETDATE(), 127)
2023-08-18 17:16:35.587	130	2 صفر 1445 5:16:35:587PM	SELECT CONVERT(NVARCHAR, GETDATE(), 130)
2023-08-18 17:16:35.587	131	2/02/1445 5:16:35:587PM	SELECT CONVERT(NVARCHAR, GETDATE(), 131)

Script Explanation

Let's walk through the SQL script step by step:

1. Variable Declarations:

   • @counter INT = 0;: Initializes a counter variable to track the formatting style numbers.
   • @date DATETIME = GETDATE();: Retrieves the current date and time and assigns it to the @date variable.

2. Temporary Table Creation and Dropping:

   • DROP TABLE IF EXISTS #FormatDateDemo: If the temporary table #FormatDateDemo already exists, it is dropped. This ensures that any previous version of the table is removed before creating a new one.
   • CREATE TABLE #FormatDateDemo ...: Creates a temporary table named #FormatDateDemo with three columns: StyleNumber, StyleOutput, and Query.

3. While Loop:

   • WHILE (@counter <= 131): Initiates a loop that continues as long as the @counter value is less than or equal to 131.

4. Loop Body:

   • BEGIN TRY: Starts a try block for error handling within the loop.

     • INSERT INTO #FormatDateDemo ...: Inserts a row into the #FormatDateDemo table with the following values:

       • StyleNumber: The current value of the @counter variable.
       • StyleOutput: The result of converting the @date using the current @counter as the formatting style.
       • Query: A string that represents the SQL query to get the same formatted datetime value.

     • SET @counter = @counter + 1;: Increments the @counter variable by 1.

   • BEGIN CATCH: Catches any errors that might occur during the try block.

     • SET @counter = @counter + 1;: Increments the @counter variable even if an error occurs, ensuring the loop continues to the next iteration.

5. Temporary Table Query:

   • SELECT * FROM #FormatDateDemo;: Retrieves all rows from the #FormatDateDemo temporary table.

In summary, this script generates formatted datetime values using different styles (0 to 131) and stores the style number, formatted output, and corresponding query in a temporary table. The loop iterates through the styles, attempting to insert formatted datetime values into the temporary table. Any errors are caught, and the loop continues to the next iteration. Finally, the script retrieves the results from the temporary table. This script can be useful for understanding how different formatting styles affect datetime values in SQL Server and for generating sample queries.