Setting date/time in SQL using int values?

Question

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 from the int values to the datetime?

Answer 1

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).

Answer 2

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)))