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

Question

Possible Duplicate:
Create a date with T-SQL

Hi,

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?

Thanks,

b.

Answer 1

SELECT CAST(CAST(year AS varchar) + '/' + CAST(month AS varchar) + '/' + CAST(day as varchar) AS datetime) AS MyDateTime
FROM table

Answer 2

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 3

You could convert your values into a 'Decimal' datetime and convert it then to a real datetime column:

select cast(rtrim(year *10000+ month *100+ day) as datetime) as Date from DateTable

See here as well for more info.

Answer 4

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

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