SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]

Question

Possible Duplicate:
Create a date with T-SQL

I've a data table that stores each year, month and day value as ints:

year | month | day 2009 |   1   |  1  2008 |  12   |  2 2007 |   5   |  5 

I need to convert it to datetime value, because I need to use it in a datetime between operation. How could I do this?

Answer 1

In order to be independent of the language and locale settings, you should use the ISO 8601 YYYYMMDD format - this will work on any SQL Server system with any language and regional setting in effect:

SELECT    CAST(       CAST(year AS VARCHAR(4)) +       RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +       RIGHT('0' + CAST(day AS VARCHAR(2)), 2)     AS DATETIME) 

Answer 2

Pure datetime solution, does not depend on language or DATEFORMAT, no strings

SELECT     DATEADD(year, [year]-1900, DATEADD(month, [month]-1, DATEADD(day, [day]-1, 0))) FROM     dbo.Table