sql server - Convert multiple integer columns into a datetime value

Question

I have the above table and I want to create a DATETIME value from it.

I've tried a number of ways using CONCAT, CONVERT and CAST to glue these together in a way that produces a datetime value and can't get there. I can produce a string that will convert like this.

CONCAT(
    '''',
    Year, 
    '-', 
    CASE LEN(Month) WHEN 1 THEN CONCAT('0', LTRIM(RTRIM(STR(Month)))) ELSE CAST(Month AS VARCHAR(2)) END,
    '-', 
    CASE LEN(Day) WHEN 1 THEN CONCAT('0', LTRIM(RTRIM(STR(Day)))) ELSE CAST(Day AS VARCHAR(2)) END,
    ' ', 
    Hour, 
    ':00:00.000',
    ''''
)

However, I wrap that output in a call to cast or convert it fails.

Can someone point me in the right direction?

Answer 1

Select Cast(Convert(nvarchar,[year])+'-'+Convert(nvarchar,[month])+'-'+Convert(nvarchar,[day])+' '+Convert(nvarchar,[hour])+':00:00.00' as datetime) from [YOUR TABLE]