Setting date/time in SQL using int values?

I have the following integer type values in a SQL script: @year, @month, @day. Now I want to convert those into a datetime value. Should be easy, right?

Well, I just went through the SQL documentation and was very surprised that I couldn't find any way to do this, other than converting to a string and then to a datetime.

declare @dt datetime
set @dt= convert(varchar,@year)+'/'+convert(varchar,@month)+'/'+convert(varchar,@day)

This is horrible! Surely there has to be a way to convert straight fro开发者_运维知识库m the int values to the datetime?

Not out of the box, but you could create a UDF that does it, for example:

create function ints2date (@year int, @month int, @day int) 
returns datetime 
as begin
    declare @foo varchar (10)

    set @foo = convert (varchar, @year) + '-' +
               convert (varchar, @month) + '-' +
               convert (varchar, @day)
    return convert (datetime, @foo)
end
go               

select dbo.ints2date (2000,1,1)

You can also do it in a more convoluted (but probably slightly faster) way using dateadd/datepart. An example of this can be found at Create a date with T-SQL (stackoverflow.com).

You can do it without converting to string like this. It's not a single function, which would be nice, but it works:

DECLARE
    @year   SMALLINT,
    @month  TINYINT,
    @day    TINYINT,
    @d      DATETIME

SET @year = 2010
SET @month = 6
SET @day = 23
SET @d = '1900-01-01'

SELECT
    DATEADD(dy, @day - 1, DATEADD(mm, @month - 1, DATEADD(yy, @year - 1900, @d)))

Another shortcut method

DECLARE 
    @year   SMALLINT, 
    @month  TINYINT, 
    @day    TINYINT, 
    @d      DATETIME 

SET @year = 2010 
SET @month = 6 
SET @day = 23 

SELECT cast(cast(@year*10000+@month*100+@day  as char(8)) as datetime)

SELECT cast(ltrim(@year*10000+@month*100+@day)  as datetime)