Search code examples
sqlsql-serversql-server-2008sql-view

Convert multiple columns (year,month,day) to a date

I've got a few columns that I'm trying to convert to one, but I'm having some issues here.

The problem is that the Month or day can be single digit, and I keep losing that 0.

I'm trying it on a view first before I do the conversion, but can't even get the three columns to give a string like this 20090517.

Any ideas? CAST and RIGHT doesn't seem to be doing it for me.

Solution

  • Alternatively

    DECLARE @YEAR int
DECLARE @MONTH int
DECLARE @DAY int

SET @YEAR = 2013
SET @MONTH = 5
SET @DAY = 20

SELECT RIGHT('0000'+ CONVERT(VARCHAR,@Year),4) + RIGHT('00'+ CONVERT(VARCHAR,@Month),2) + RIGHT('00'+ CONVERT(VARCHAR,@Day),2)

    Gives

    20130520