问题描述
我想将一列 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)来自 mytableI 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
这篇关于将列作为参数传递给 SQL Server 中的 dateadd的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！