0

I am trying to pull data from SENT data view based on date. What format of date should I use?

I have tried this but no results:

SELECT *
FROM _Sent WITH (NOLOCK)
WHERE EventDate = '2017-05-29'

I have tried this but no results:

SELECT *
FROM _Sent WITH (NOLOCK)
WHERE EventDate = '05-29-2017'

Tried this too but no results:

SELECT *
FROM _Sent WITH (NOLOCK)
WHERE EventDate =  '5/29/2017'

Looking at the data extension, the data seems be in this format "Tuesday, May 29, 2017 7:02 AM"

4

EventDate is a datetime - "2017-05-30 22:35:27.122" which is not the same as "2017-05-30" (midnight on 30th May). Try evaluating against:

cast(EventDate As Date)

This truncates EventDate so you're comparing like for like...

WHERE cast(EventDate As Date) = cast('2017-05-29' as Date)

|improve this answer|||||
4

According to the documentation article Query: Find Subscribers by Date or Time Frame the date format to use is 'YYYY-MM-DD' so in your case '2017-05-29' should be correct.

However in the documentation on Data View: Sent it is mentioned to be a datetime-value. As in the background Transact-SQL is used, you could cast the value with added time in your SQL-statement and therefore need to use a range from 00:00 to 24:00. Here you can find some examples: datetime (Transact-SQL) - Examples

SELECT *
FROM _Sent WITH (NOLOCK)
WHERE EventDate between CAST('2017-05-29 00:00:00.000' AS datetime) AND CAST('2017-05-29 24:00:00.000' AS datetime)

Another option would be using this query:

SELECT *
FROM _Sent WITH (NOLOCK)
WHERE CAST(EventDate AS Date) = '2017-05-29'
|improve this answer|||||
  • You wrap it in quotes? Interesting. – Adrian Larson May 30 '17 at 15:56
  • At least that's how it is written in the marketing cloud documentation article I referred to. – Markus Slabina May 30 '17 at 16:00
  • I have tried this query already but getting no result (we have one mailing that went out on May 29, 2017 , I am looking at it on tracking page) SELECT * FROM _Sent WITH (NOLOCK) WHERE EventDate = '2017-05-29' – kuml May 30 '17 at 16:08
  • Markus - I tested your second option as well and it worked WHERE CAST(EventDate AS Date) = '2017-05-29' . Thank you! – kuml May 30 '17 at 16:27

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.