Using CAST and CONVERT Functions with Dates

CAST and CONVERT can both be used to switch a value to a new data type. They are similar, but certainly not identical. While CAST is considered ANSI SQL and will get you across the finish line, CONVERT can give you more flexibility when it comes to formatting date values. Let’s look at an example comparing the usage of CAST and CONVERT with dates.

CAST Date

CAST keeps it simple. If I need a date value to be of a different type, I can use CAST to get that done. What if I need to add a date to a string:

DECLARE @MyDate DATE = '2023-10-12';
DECLARE @Msg AS VARCHAR(50) = 'The date is ';

SELECT @Msg + @MyDate;

This will throw an error because SQL Server does not implicitly convert the data type:

Msg 402, Level 16, State 1, Line 4
The data types varchar and date are incompatible in the add operator.

But I can use CAST to complete my SELECT statement:

DECLARE @MyDate DATE = '2023-10-12';
DECLARE @Msg AS VARCHAR(50) = 'The date is ';

SELECT @Msg + CAST(@MyDate AS VARCHAR(50)) AS 'CAST_Date';

CONVERT Date

What if you need to display the name of the month in the result or want the date to be in month-day-year format? This is where CONVERT can shine. CONVERT allows you to include an argument to specify the format the date should use.

We want to see “Oct” when we view this date, so let’s use CONVERT. In this case, we’ll need to use format 107:

DECLARE @MyDate DATE = '2023-10-12';
DECLARE @Msg AS VARCHAR(50) = 'The date is ';

SELECT @Msg + CONVERT(VARCHAR, @MyDate, 107) AS 'CONVERT_Date_107';

Let’s try one more. We’ll display our date in the month-day-year format by using format 110:

DECLARE @MyDate DATE = '2023-10-12';
DECLARE @Msg AS VARCHAR(50) = 'The date is ';

SELECT @Msg + CONVERT(VARCHAR, @MyDate, 110) AS 'CONVERT_Date';

The full list of formats can be found here.

Save the Date

CAST and CONVERT are both useful for data type conversions. If you’re working with dates, consider the options CONVERT can give you as it relates to formatting. If you don’t care about the additional formatting options and just need a quick conversion, CAST will get you by.

Thanks for reading!