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)