在SQL中将date（smalldatetime）和hour（tinyint）列转换为datetime

I have two columns (Date column which is smalldatetime and Hour column which is smallint) in one table of SQL server database as shown below

我在SQL Server数据库的一个表中有两列(Date列是smalldatetime,Hour列是smallint),如下所示

ScenarioId  ZoneId  Date    Hour    DemandMW    Hour
618011031   71400   2018-01-01 00:00:00 1   2490.6  2018-01-02 00:00:00
618011031   71400   2018-01-01 00:00:00 2   2405.8  2018-01-03 00:00:00
618011031   71400   2018-01-01 00:00:00 3   2364    2018-01-04 00:00:00
...
618011031   71400   2018-01-01 00:00:00 4   2358    2018-01-05 00:00:00

I like to create another datetime column combining the two as:

我想创建另一个datetime列,将两者组合为:

ScenarioId  ZoneId  Date    Hour    DemandMW    Hour DateTime
618011031   71400   2018-01-01 00:00:00 1   2490.6  2018-01-02 00:00:00 2018-01-02 01:00:00
618011031   71400   2018-01-01 00:00:00 2   2405.8  2018-01-03 00:00:00 2018-01-02 02:00:00
618011031   71400   2018-01-01 00:00:00 3   2364    2018-01-04 00:00:00 2018-01-02 03:00:00
...
618011031   71400   2018-01-01 00:00:00 24  2358    2018-01-05 00:00:00 2018-01-02 24:00:00

How to write the select query. Please advice.

如何编写选择查询。请指教。

1 个解决方案

#1

Have you tried dateadd()?

你试过dateadd()吗?

select dateadd(hour, [hour], [date]) as [datetime]

If you want this as part of the table, then add a computed column:

如果您希望将此作为表的一部分,则添加计算列:

alter table t add [datetime] as (dateadd(hour, [hour], [date]));

Note: date, datetime, and hour are really bad names for columns because these are all SQL Server keywords.

注意:日期,日期时间和小时是列的错误名称,因为这些都是SQL Server关键字。

#1