3

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"

2 Answers 2

7

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)

7

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'
4
  • You wrap it in quotes? Interesting.
    – Adrian Larson
    May 30, 2017 at 15:56
  • At least that's how it is written in the marketing cloud documentation article I referred to. May 30, 2017 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, 2017 at 16:08
  • 1
    Markus - I tested your second option as well and it worked WHERE CAST(EventDate AS Date) = '2017-05-29' . Thank you!
    – kuml
    May 30, 2017 at 16:27

You must log in to answer this question.

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