Converting DateTime to VarChar in SQL Server

In SQL Server, converting a datetime value to a varchar is a common requirement. This can be achieved using the CONVERT function, which allows you to specify the style of the output string.

Understanding the CONVERT Function

The CONVERT function takes three arguments: the data type to convert to, the expression to convert, and an optional style argument. The style argument specifies the format of the output string.

For example:

DECLARE @now datetime
SET @now = GETDATE()
SELECT CONVERT(varchar(10), @now, 120) AS Output

This code converts the current date and time to a varchar string in the format ‘yyyy-mm-dd’.

Common Styles

Here are some common styles used when converting datetime to varchar:

• Style 0: ‘mon dd yyyy hh:miAM/PM’
• Style 1: ‘mm/dd/yy’
• Style 2: ‘yy.mm.dd’
• Style 3: ‘dd/mm/yy’
• Style 4: ‘dd.mm.yy’
• Style 5: ‘dd-mm-yy’
• Style 120: ‘yyyy-mm-dd hh:mi:ss’
• Style 121: ‘yyyy-mm-dd hh:mi:ss.mmm’

Note that the style argument is optional, and if not specified, the default style (0) will be used.

Trimming the Time Part

If you only need to display the date part of a datetime value, you can use the LEFT function to trim the time part. For example:

DECLARE @now datetime
SET @now = GETDATE()
SELECT LEFT(CONVERT(varchar(20), @now, 120), 10) AS Output

This code converts the current date and time to a varchar string in the format ‘yyyy-mm-dd hh:mi:ss’, and then trims the time part using the LEFT function.

Using the FORMAT Function (SQL Server 2012 and later)

In SQL Server 2012 and later, you can use the FORMAT function to convert datetime values to varchar strings. The FORMAT function allows you to specify a custom format string.

DECLARE @now datetime
SET @now = GETDATE()
SELECT FORMAT(@now, 'yyyy-MM-dd') AS Output

This code converts the current date and time to a varchar string in the format ‘yyyy-mm-dd’.

Best Practices

• Always specify the style argument when using the CONVERT function to ensure consistent output.
• Use the LEFT function to trim the time part if you only need to display the date part of a datetime value.
• Consider using the FORMAT function (SQL Server 2012 and later) for more flexibility in formatting datetime values.

By following these guidelines, you can effectively convert datetime values to varchar strings in SQL Server.