Marketing Coud: Always use CAST and CAST with date fields from Data Views?

Question

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 :

SELECT
a.subscriberkey,
CONVERT(varchar, EventDate, 20) AS EventDate,
b.emailid,
b.emailname
FROM _open AS a
INNER JOIN _job AS b
ON a.jobid = b.jobid
AND CAST(a.eventdate as date)>= CAST(getdate() -30 as date)

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.

SELECT
  a.subscriberkey
, a.EventDate
, j.emailid
, j.emailname
FROM _open a
INNER JOIN _job AS j ON j.jobid = a.jobid
where 
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.