Question on Converting ints to datetimes in TSQL

Question

I have a stored procedure that takes an int for the desired month and year as parameters. It uses these to compare some datetime values from the tables I'm pulling from. I need to conver them into DateTime values. I'm trying to do something like this:

CONVERT(DATETIME, CONVERT(varchar(4), @year) + '-' + Convert(varchar(2),@month) + '-01 00:00:00', 102))

which builds the "2009-6-1 00:00:00" string (for those parameter values). This doesn't seem to work though, any other suggestions on how to do this? I think I'm going about this too much like a programmer...

This is Sql Server 2008.

Answer 1

SELECT CAST(CAST(@Year AS VARCHAR(4))  + RIGHT('0' + CAST(@Month AS VARCHAR(2)), 2) + '01' AS DATETIME)

Should do it

Answer 2

What about:

DECLARE @year int;
DECLARE @Month int;

SET @year = 2010;
SET @Month = 1

SELECT CAST(LTRIM(@year * 10000 + @Month * 100 + 1) AS smalldatetime)