I'm trying to write a query in Marketing Cloud that will filter for an event (specifically a unique open) that took place on a specific day of the week. So for example, my query would answer the question "how many times has this subscriber uniquely opened on a Monday?".
If I look at data extensions in Contact Builder, date parameters seem to lack this information. They're usually formatted like "6/28/2019 9:14:42 AM". But if I look at the same data in the Subscribers portion of Email Studio, the same date fields do include the weekday like so: "Friday, June 28, 2019 9:14 AM". That makes me think the weekday must be stored in a data view somewhere, right?
However, if I query the _Open data view for EventDates like '%Monday%', I get 0 results. Here's an example query I've tried:
select o.SubscriberKey, o.JobID, cast(min(o.EventDate) as Date) as Date from _open o where LOWER(DatePart(dw,o.EventDate)) like '%monday%' group by o.SubscriberKey, o.JobID, o.EventDate
So my question has two parts: Is this sort of query even possible in Salesforce? If it is, how can it be done?