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. Nov 5, 2019 at 9:49
  • Thanks. And if I used DATEDIFF() how would the last line of code look like? Without CAST?
    – nix9247
    Nov 5, 2019 at 10:01
  • DATEDIFF(dd, a.EventDate, GETDATE()) >= 30 Nov 5, 2019 at 11:13
  • OK, thanks for clarifying.
    – nix9247
    Nov 5, 2019 at 11:36

1 Answer 1


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.

You must log in to answer this question.

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