I'm trying to get the Diff of two fields using SQL-CodePudding

I'm trying to get the difference of two fields using SQL, but one of my fields data type is Text and other is Date. Also text field come in UTC format and the Date field is in Mountain Standard time format.

extract_date is Date field.
src_msg_date_time is Text field

I'm getting "Automation Error" in Query studio while I'm running below query. Any help please what I'm doing wrong.!

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

CodePudding user response：

Explicit conversion from text to datetime is not allowed in t-sql, you may need to do a doble cast or conversion:

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

ref:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

CodePudding user response：

I would do you timezone conversions in a subquery to simplify the where-clause.

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

Also:

The with (nolock) unnecessary in SFMC. You don't have that level of control in your queries in the platform.
In your future SQL query questions, please include a sample of the data being selected and the desired output. This is especially important when any data-type conversions and calculations are part of the query.