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:

SELECT
    UserID_String = CAST(UserID AS VARCHAR(50))
FROM dbo.USER

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.

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
    + LEFT('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
    + LEFT('0' + CAST(DATEPART(dd, @mydate) AS VARCHAR(10)), 2)
   
    -- output: 20100604

12 comments
Deb 31 Jan 2012 at 6:41 pm

Is the word to be used really ‘append’? “you need to append the number comprising the date into a string.” Is it really a ‘padding’ of the number 0?

Bhaskar 20 Sep 2011 at 1:17 pm

Looks like it should be right instead of left

Robert 08 Nov 2011 at 8:14 am

Agree! If you use left and it’s i.e day 23 you will get ’02′ with left method.

Anonymous 24 Jun 2010 at 11:47 am

What about @mydate =’2009-12-11′, output =20090201 ?

Derek Dieter 25 Jun 2010 at 9:21 am

Hi, You can find that answer here:

http://sqlserverplanet.com/sql/cast-date-with-no-time/

SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629

Anonymous 14 Apr 2011 at 10:56 pm

Any date with double digit month and/or day is incorrect with this method (0 + left(x,2) will always start with 0)

Featured Articles

 Site Author