将列作为参数传递给 SQL Server 中的 dateadd

我想将一列 UTC 时间转换为本地时间.

我的数据如下所示:

time_utc TZID 时区------------------------------------------------2014-02-27 12:00:39.0 美国/多伦多-52013-05-21 09:35:30.0 America/Goose_Bay -42015-01-08 06:58:58.0 美国/克雷斯顿 -7

我知道使用

select *, DATEADD(hour, 5,time_utc)来自 mytable

将向 time_utc 列添加 5 小时.

但是，如您所见，我有一个可变时区列.

如何将此变量传递给 dateadd 函数?

我尝试了以下 2 个命令，但它们不起作用:

尝试 #1:

select *, DATEADD(hour, timezone, time_utc)来自 mytable

尝试 #2:

select *, DATEADD(hour, (select timezone from mytable), time_utc)来自 mytable

两者都抛出这个错误:

<块引用>

参数数据类型 varchar 对 dateadd 函数的参数 2 无效.[SQL 状态=S0001，数据库错误代码=8116]

对于时区的十进制值，例如 -3.5，这将如何工作?

谢谢

解决方案

如何将此变量传递给 datetime 函数?

只需在函数调用中引用列:

select *, DATEADD(hour, timezone, time_utc)来自 mytable

<块引用>

对于时区的十进制值，例如 -3.5，这将如何工作?

DATEADD 的数字"参数采用整数，因此您必须更改为分钟并缩放小时偏移量.由于您的 timezone 列显然是一个 varchar 列，因此也将其转换为十进制值:

select *, DATEADD(minute, cast(timezone as decimal(4,2)) * 60 , time_utc)来自 mytable

I want to convert a column of UTC time to local time.

My data looks like this:

time_utc                TZID            timezone
------------------------------------------------
2014-02-27 12:00:39.0   America/Toronto     -5
2013-05-21 09:35:30.0   America/Goose_Bay   -4
2015-01-08 06:58:58.0   America/Creston     -7

I know that using

select *, DATEADD(hour, 5,time_utc)
from mytable

will add 5 hours to column time_utc.

However, as you can see, I have a variable time zone column.

How can I pass this variable to the dateadd function?

I tried the following 2 commands but they don't work:

Attempt #1:

select *, DATEADD(hour, timezone, time_utc)
from mytable

Attempt #2:

select *, DATEADD(hour, (select timezone from mytable), time_utc)
from mytable

Both throws this error:

Argument data type varchar is invalid for argument 2 of dateadd function. [SQL State=S0001, DB Errorcode=8116]

For decimal values of timezone, for instance -3.5, how would this work?

Thanks

解决方案

How can I pass this variable to datetime function?

Just reference the column in the function call:

select *, DATEADD(hour, timezone, time_utc)
from mytable

For decimal values of timezone, for instance -3.5, how would this work?

The "number" parameter of DATEADD takes an integer, so you'd have to change to minutes and scale the hour offset. Since your timezone colume is apparently a varchar column, convert it to a decimal value as well:

select *, DATEADD(minute, cast(timezone as decimal(4,2)) * 60 , time_utc)
from mytable