1

I'm trying to obtain data from the data view for the previous hour. So if it's 15:30 now, I'd like to check between 14:00 and 15:00 I've ended up with using FORMAT AND DATEADD and it works when used in SELECT, but when I move it to WHERE clause to compare with EventDate from DataView it gives "automation error".

SELECT
EventDate
From _Sent
WHERE
EventDate >= FORMAT(DATEADD(HOUR, -1, GETUTCDATE()), 'dd/MM/yyyy HH:00:00')
AND EventDate < FORMAT(GETUTCDATE(), 'dd/MM/yyyy HH:00:00')

I also tried CASTing as DATETIME, but also running into same error. Is that even achievable using FORMAT function?

4
  • 4
    When doing a date data type comparison to another date data type, doing format will likely blow it up. Format usually outputs a string/text data type, which would then open up risk of failure if the SQL processor cannot then recognize the string as a date prior to comparison and then will send a data type mismatch which can cause an error. Try removing the format in the where clause and see if this works. Aug 19, 2022 at 14:14
  • Thanks, it will work without the FORMAT however then I won't be able to round the current time to the past hour, so that I can check data from the previous, full hour :)
    – Rad
    Aug 22, 2022 at 6:31
  • 1
    Instead of working with just one query directly on _Sent, you could select and round EventDate in step 1, store that in a "staging" DE. Then in step 2, base your comparison query on the rounded eventDate staging DE as a followup. Aug 22, 2022 at 6:53
  • Thanks @JonasLamberty, that's what I probably end up doing, I just wanted to avoid creating a trail of almost empty config DEs :) Or do SSJS instead.
    – Rad
    Aug 22, 2022 at 7:05

1 Answer 1

0

I figured it out. The following code works just fine:

SELECT
EventDate
From _Sent
WHERE
EventDate >= CONVERT(DATETIME2, FORMAT(DATEADD(HOUR, -1, GETUTCDATE()), 'dd/MM/yyyy HH:00:00'), 103)
AND EventDate < CONVERT(DATETIME2, FORMAT(GETUTCDATE(), 'dd/MM/yyyy HH:00:00'), 103)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .