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]

8 comments
zyko 27 Mar 2015 at 3:31 pm

took me a while to check what’s going on:
I think you wanted RIGHT instead of LEFT
*ggg*

pawan 31 Mar 2014 at 3:27 pm

script doesnot work for all months…all days…in a month..it gives wrong output..

select cast (datepart(YEAR, getdate()) as varchar)
+ LEFT(‘0’+cast(datepart(MM, getdate())as varchar),2)
+ left(‘0’+cast(datepart(DAY, getdate()) as varchar),2)

———— ————————————————————

output.
20140303 but date is 31st March 2014

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

  • Thanks for visiting!
css.php