Get a datetime from year, day of the year and hour in SQL Server 2008 R2

Question

I have a table in a Microsft SQL Server 2008 R2, which came from an outside source. The columns of the are as follows: ID, Year, DAY, HOUR & Value, where DAY contains the day of the year (from 1 to 366) and HOUR represents the hour of the day (from 0 to 23).

I wish to create a new datetime column and populate it with the dateTime created from the data in Year, DAY & HOUR columns.

What SQL function should I use to create the DateTime from its parts?

SQL Server 2012 has DATETIMEFROMPARTS, but there is no equivalent function for SQL Server 2008 R2

Answer 1

declare @Year int = 2003
declare @Day int = 100
declare @Hour int = 13

select dateadd(hour, @Hour, dateadd(dayofyear, @Day - 1, dateadd(year, @Year - 1900, 0)))