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

Check out: http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion — George Johnston, Apr 30 '13 at 11:21

score 7 · Accepted Answer · answered Apr 30 '13 at 11:32

7

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

answered Apr 30 '13 at 11:32

Mikael Eriksson

136,425
22
210
281

Thanks a ton! I did not know that 0 represents 1900-01-01 00:00:00.000. – Devdatta Tengshe Apr 30 '13 at 11:41

KaraokeStu · Answer 2 · 2013-04-30T11:47:21.580

4

You could use the following instead:

DECLARE @day int, @month int, @year int
SELECT @day = 4, @month = 3, @year = 2011

SELECT dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)

That will give you your date (though it is true that SQL 2012 gets it right, finally!)

To use it when you have year, day of the year and hour of the day, use the following:

declare @year int, @dayofyear int, @hourofday int
select @year = 2013, @dayofyear = 120, @hourofday = 12

select dateadd(hh, @hourofday, dateadd(yy, @year - 1900, dateadd(dd, @dayofyear - 1, 0)))

edited Apr 30 '13 at 11:47

answered Apr 30 '13 at 11:27

KaraokeStu

758
7
17

I don't have month, or day of the month. I have only Year, day of the year, and hour of the day. – Devdatta Tengshe Apr 30 '13 at 11:34
I've updated to use your available fields, which could probably be shortened, but it works nonetheless! – KaraokeStu Apr 30 '13 at 11:48

score 3 · Answer 3 · answered Oct 08 '13 at 00:29

Created that for myself and thought it would be a good place to share - it is based on the sample by Mikael Eriksson.

CREATE FUNCTION [dbo].[DATETIME2FROMPARTS](
    @year int,
    @month int,
    @day int,
    @hour int,
    @minute int,
    @second int,
    @fractions int,
    @precision int)
RETURNS datetime2(7)
AS
BEGIN
    RETURN
        DATEADD(NANOSECOND, POWER(10, 9-@precision)*@fractions, 
        DATEADD(SECOND, @second, 
        DATEADD(MINUTE, @minute, 
        DATEADD(HOUR, @hour, 
        DATEADD(DAY, @day-1, 
        DATEADD(MONTH, @month-1, 
        DATEADD(YEAR, @year-1900, 
        CAST(CAST(0 AS datetime) AS datetime2(7)))))))));
END

score 0 · Answer 4 · answered Apr 30 '13 at 11:37

Here's an alternative solution:

create table yourtable (yr int, dy int, hr int);
insert into yourtable values (2013,100,5);
insert into yourtable values (2013,1,1);

select dateadd(hour,hr,dateadd(month, (yr - 1900) * 12 , dy - 1))
from yourtable

The concept is add the hours to the date, using the year as (year - 1900) * 12 as the month, beginning with the number of days.

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

4 Answers4

Linked