問題描述
我想將一列 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！