SQL Server Cast

The SQL Server cast function is the easiest data type conversion function to be used compared to the CONVERT function. It takes only one parameter followed by the AS clause to convert a specified value.

A quick example is the following:

[cc lang=”sql”]
SELECT
UserID_String = CAST(UserID AS varchar(50))
FROM dbo.User
[/cc]

This example will convert the integer to a character value. So what is the use of this? Well, more likely that not if you’re on this page you know the use, however it is usually used to concatenate or perform string like comparisons on numeric values, or to convert a numeric or string value in order to compare.

An example is when trying to manually construct date parts into a custom format, you need to append the number comprising the date into a string. Say you wanted to make the date 2010/06/05 into a custom format of: 20100605. In order to do so, you need to utilize the datepart function then concatenate the results. The problem is, when you try to concatenate the results of datepart, instead of concatenating, the number get summed together. To get around this, simply use CAST.

[cc lang=”sql”]
DECLARE @mydate as DATETIME
SET @mydate = GETDATE()

— wrong way
SELECT DATEPART(yy, @mydate) + DATEPART(mm, @mydate) + DATEPART(dd, @mydate)

— output: 2020

— correct way
SELECT
CAST(DATEPART(yy, @mydate) AS VARCHAR(10))
— append a zero before the date so we get two digits on a one digit month
+ RIGHT(‘0’ + CAST(DATEPART(mm, @mydate) AS VARCHAR(10)), 2)
— append a zero before the date so we get two digits on a one digit day
+ RIGHT(‘0’ + CAST(DATEPART(dd, @mydate) AS VARCHAR(10)), 2)

— output: 20100604
[/cc]