Concatenating Dates in SQL

I was working on a database where the original developer had stored dates in an Integer typed column in the format of YYYYMMDD, so today would look like 20060830.

This means whenever I want to filter or do something by date, I need to convert to that format. No biggie.

But I need to write a SQL statement for a stored procedure that would go through the records in one of the tables and set an Active flag to 0 (off) for evey record where the Date_End was earlier than today's date.

In SQL Server you get the date with GetDate(). You can break out the Year, Month and Day respectively by using Year(GetDate()), Month(GetDate()), and Day(GetDate()). I found out when I tried to to concatenate these that they end up adding together. So:

Year(GetDate())+Month(GetDate())+Day(GetDate())

Ended up as 2044 (2006 + 8 + 30).

In order to join them (not add them) I had to convert them to character strings. But that brought up another issue: if the month or the day was less than 10, it ended up as a single digit. I needed it to be preceeded by a 0 if it was less than 10.

My Stored procedure ended up looking like:

UPDATE Table
SET Active = 0
WHERE
Date_End <
CAST(Year(getDate()) AS varchar(4))+
RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+
RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)

Broken down, here's how it works:

1. CAST the Year as a 4 character varchar

2. Put a 0 in front of the month, cast the Month as a 2 character varchar, then grab the right 2 digits

3. Put a 0 in front of the day, cast the Day as a 3 character varchar, then grab the right 2 digits

I set the stored procedure to execute evey night just after midnight (server time).