When querying SF Marketing Cloud data views, specifically its date-type fields (e.g. EventDate), Salesforce support suggested we should always use SQL functions: CONVERT (e.g. varchar, EventDate, 20), followed by CAST (e.g EventDate AS date) if we need to get a specific time period.

Is this really needed or can we get by without CAST if the date-format is already correct in a data view (e.g. for EventDate)?

Here's an example of a SQL we're using based on that SF advice :

CONVERT(varchar, EventDate, 20) AS EventDate,
FROM _open AS a
ON a.jobid = b.jobid
AND CAST(a.eventdate as date)>= CAST(getdate() -30 as date)
  • EventDate and GETDATE() is of type datetime. So if you want to compare two dates, you need to cast to date. Otherwise it will be hard to match the two values. Personally I prefer using DATEDIFF() when working with comparing dates and time periods. – user3274635 Nov 5 '19 at 9:49
  • Thanks. And if I used DATEDIFF() how would the last line of code look like? Without CAST? – nix9247 Nov 5 '19 at 10:01
  • DATEDIFF(dd, a.EventDate, GETDATE()) >= 30 – user3274635 Nov 5 '19 at 11:13
  • OK, thanks for clarifying. – nix9247 Nov 5 '19 at 11:36

Casting/Converting a DateTime data type to a Date effectively removes the time portion of the DateTime value.

Doing a cast or convert on both sides of a conditional in a where-clause is very inefficient (not sargable). The data views are notoriously slow, so it's wise to write your conditionals as simple as you can.

Something like this will run faster. Plus you can just subtract days directly from GetDate() without an additional function.

, a.EventDate
, j.emailid
, j.emailname
FROM _open a
INNER JOIN _job AS j ON j.jobid = a.jobid
a.eventDate >= convert(date, getDate()-30)
and a.eventDate < convert(date, getDate()-29)

If you need some more examples, there are several other examples around here.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.