我正在嘗試使用 SQL 獲取兩個欄位的差異，但我的欄位資料型別之一是文本，另一個是日期。此外，文本欄位采用 UTC 格式，日期欄位采用山地標準時間格式。
- extract_date 是日期欄位。
- src_msg_date_time 是文本欄位
當我在查詢下運行時，我在 Query Studio 中收到“自動化錯誤”。任何幫助請我做錯了什么。！
SELECT
A.extract_date AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC' AS EventDate,
B.SmsStandardStatusCode,
B.Description,
A.src_msg_date_time,
A.SubID
FROM
[SmsSendLog] A WITH(NOLOCK)
JOIN
[CustomDataView] B WITH(NOLOCK)
ON A.subid=B.SUBSCRIBERID and a.smsjobid=b.SMSJOBID and a.smsbatchid=b.smsbatchid
where DATEDIFF(Day,A.extract_date,GETDATE()) between 0 and 5
and
DATEDIFF(hour,(Convert(Datetime, A.src_msg_date_time,120) AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC'),(A.extract_date AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC')) between 0 and 2
uj5u.com熱心網友回復：
t-sql 中不允許從文本到日期時間的顯式轉換，您可能需要進行雙重轉換或轉換：
SELECT
A.extract_date AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC' AS EventDate,
B.SmsStandardStatusCode,
B.Description,
A.src_msg_date_time,
A.SubID
FROM
[SmsSendLog] A WITH(NOLOCK)
JOIN
[CustomDataView] B WITH(NOLOCK)
ON A.subid=B.SUBSCRIBERID and a.smsjobid=b.SMSJOBID and a.smsbatchid=b.smsbatchid
where DATEDIFF(Day,A.extract_date,GETDATE()) between 0 and 5
and
DATEDIFF(hour,(Convert(Datetime, cast(A.src_msg_date_time as varchar(max)),120) AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC'),(A.extract_date AT TIME ZONE 'Mountain Standard Time' AT TIME ZONE 'UTC')) between 0 and 2
參考：
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
uj5u.com熱心網友回復：
我會在子查詢中進行時區轉換以簡化 where 子句。
SELECT
sl.extract_date EventDate
, dv.SmsStandardStatusCode
, dv.Description
, sl.src_msg_date_time
, sl.SubID
FROM (
select
Convert(Datetime, sl0.src_msg_date_time,120) AT TIME ZONE 'UTC' as src_msg_date_time
, sl0.extract_date AT TIME ZONE 'UTC' as extract_date
, convert(date, getDate()) AT TIME ZONE 'UTC' as today
, sl0.subid
from SmsSendLog sl0
) sl
inner join CustomDataView dv ON sl.subid = dv.SUBSCRIBERID and sl.smsjobid = dv.SMSJOBID and sl.smsbatchid = dv.smsbatchid
where
sl.subid is not null
and sl.src_msg_date_time is not null
and sl.extract_date is not null
and sl.extract_date >= convert(date, sl.today-5)
and datediff(hour,sl.src_msg_date_time,sl.extract_date) between 0 and 2
還：
- 將
with (nolock)在SFMC不必要的。您在平臺中的查詢中沒有這種級別的控制。 - 在您以后的 SQL 查詢問題中，請包括所選資料的示例和所需的輸出。當任何資料型別轉換和計算是查詢的一部分時，這一點尤其重要。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/398223.html
標籤：sql 查询语句 自动化 salesforce-marketing-cloud