从SQL Server 2008 R2中的年，日和小时获取日期时间

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

我在Microsft SQL Server 2008 R2中有一个表,它来自外部源。其列如下:ID,年,日,HOUR和值,其中DAY包含一年中的某一天(从1到366),HOUR表示一天中的小时(从0到23)。

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

我希望创建一个新的datetime列,并使用从Year,DAY和HOUR列中的数据创建的dateTime填充它。

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

我应该使用什么SQL函数从其部分创建DateTime?

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

SQL Server 2012具有DATETIMEFROMPARTS,但SQL Server 2008 R2没有等效功能

4 个解决方案

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

#2

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

这会给你你的约会(尽管SQL 2012确实是正确的,最后!)

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

#3

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

为我自己创造并认为这将是一个分享的好地方 - 它是基于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

#4

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.

这个概念是将日期添加到日期,使用年份(年 - 1900)* 12作为月份,从天数开始。

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

#2