How to convert DATETIME to VARCHAR in Microsoft SQL Server? Example Tutorial

One of the most common, yet trick for many programmer, is to convert a DATETIME column into VARCHAR i.e. String in SQL Server. Suppose, in People table, you have a datetime column, birthdate and you want to print the name and birthdate into dd-MM-yyyy format, you need to convert the datetime column into varchar. You can use the CONVERT() function to do this job. It accepts three arguments, the data, the output type and the format in which you want your VARCHAR date e.g. to convert birthdate of a person into dd-MM-yyyy, we need to use the formatting style 1.

DATETIME to VARCHAR using CONVERT in SQL Server

Now, let's see the actual code you can use to convert a datetime value to string or varchar in SQL server based upon different formatting style:

Here is the SQL :

SELECT CONVERT(CHAR(8), GETDATE(), 3) -- dd/MM/yy format

union

SELECT CONVERT(CHAR(10), GETDATE(), 103) -- dd/MM/yyyy format

union

SELECT CONVERT(CHAR(10), GETDATE(), 104) -- dd.MM.yyyy format

union

SELECT CONVERT(CHAR(10), GETDATE(), 105) -- dd-MM-yyyy format

Output

(No column name)

08-10-2015

08.10.2015

08/10/15

08/10/2015

Though, you need to remember important formatting style which is not easy, hence here is a nice memory map of different SQL Server formatting style divided in date and time parameters

DateTime to Varchar using CAST() function

You can also use the cast() function of T-SQL to convert datetime value to varchar in SQL Server. Though, the CAST function in SQL Server doesn't support date formatting directly like the CONVERT function does. 

However, you can achieve similar results by first converting the date to a string in a particular format and then using substring functions to manipulate the output as needed. 

Here's how you can rewrite above sql query using CAST:

SELECT 
    CAST(YEAR(GETDATE()) AS CHAR(4)) + '/' + 
    CAST(MONTH(GETDATE()) AS CHAR(2)) + '/' + 
    CAST(DAY(GETDATE()) AS CHAR(2)) AS DateFormatted1
UNION
SELECT 
    CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + 
    RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '-' + 
    RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR(2)), 2) AS DateFormatted2
UNION
SELECT 
    CAST(YEAR(GETDATE()) AS CHAR(4)) + '.' + 
    RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '.' + 
    RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR(2)), 2) AS DateFormatted3
UNION
SELECT 
    CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + 
    RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '-' + 
    RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR(2)), 2) AS DateFormatted4

Each SELECT statement formats the date in a different way using string manipulation. Here's the output for each:

DateFormatted1: Year/Month/Day (e.g., "2024/05/26")

DateFormatted2: Year-Month-Day (e.g., "2024-05-26")

DateFormatted3: Year.Month.Day (e.g., "2024.05.26")

DateFormatted4: Year-Month-Day (same as DateFormatted2, included to demonstrate versatility)

This approach uses string concatenation and padding functions to format the date, which is necessary since CAST does not provide direct date formatting options like CONVERT does​

Important points

Here are few important points which is wroth remembering while converting datetime column to varchar in SQL Server

1. You need to remember the formatting style. You can see this this page to see various date format style supported by Microsoft SQL Server as shown below:

2. You can also use the CAST function, but it doesn't allow you to define a formatting style.

That's all about how to convert datetime to varchar in SQL Server. As seen, you can use either convert() or cast to convert a datetime value to varchar. Only difference is that convert() support direct formatting style and cast() doesn't, hence convert() function is almost always a better choice then cast function for datetime to varchar conversion. 

Other SQL Tutorials and Articles you may like

• 6 Examples of Correlated subqueries for beginners
• How to get rows between two given dates in MSSQL
• How to remove duplicate rows from table in SQL Server
• How to use WHERE and HAVING clause in SQL
• How to convert String to Date in SQL?
• Difference between Primary key and Unique key
• Does order of columns matter in Composite index?
• Difference between Self and Equi join in SQL
• 10 Example of SELECT queries in SQL
• How to create and rollback transaction in database?
• 4 Examples of CASE expression in SQL Server
• Difference between UNION and UNION ALL in SQL
• 10 Examples of ALTER command in SQL

Thank you for reading this article so far. Feel free to ask if you have any doubts or questions, happy to answer any query.