Correct Syntax in AEP Query Service

[RyanMoravick]

Hey there, 

Im trying to run a query in Query Service and trying to understand the correct syntax for looking at certain dates. I want to look at WHERE transactionDate = '2024-09-04'. However, it returns no results. When I run WHERE transactionDate BETWEEN '2024-09-04' AND '2024-09-05', I can see results for 2024-09-04. So my question is when looking at a single date, what is the correct syntax to use?

Thanks!

[ccg1706 · Accepted solution]

Hi @RyanMoravick ,

The issue might arise if the transactionDate may  include date and time. 

To manage this you can try with the following options:

-Using DATE(): WHERE DATE(transactionDate)='2024-09-04'

-Using a range of time to cover the entire day: WHERE transactionDate >= '2024-09-04' AND transactionDate <  '2024-09-05'

If using the Date function doesn't give you the expected result you can try with CAST or TRUNC.

-CAST: WHERE CAST(transactionDate AS DATE) = '2024-09-04'

-TRUNC: WHERE TRUNC(transactionDate) = '2024-09-04'

¡Hope this helps!

Regards, 

Celia

[RyanMoravick]

Hi @ccg1706 I used your first solution and it worked. Thank you!

[ccg1706]

Hi @RyanMoravick,

Great, as always happy to help.

Regards, 

Celia

[RyanMoravick]

Hey @ccg1706 is there a syntax for looking at YESTERDAY if I wanted to look at the previous day, instead of a specific date?

Thanks!

[brekrut]

You can use  current_date-1 to grab the date of yesterday.  

[RyanMoravick]

Thank you!

[ccg1706]

Hi @RyanMoravick,

You can use the following ones:

WHERE transactionDate = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)

WHERE transactionDate = CURRENT_DATE - 1

Both methods should give you the results from yesterday.

¡Hope this is helpful!

Regards, 

Celia

[RyanMoravick]

Thank you!

[Indra]

select * from xxxxxxxevent_dataset_for_websitexxxxx where DATE(TIMESTAMP) = CURRENT_DATE-115

[RyanMoravick]

Hey @ccg1706 one more syntax question for you. Do you know how to convert the timestamp in a query from UTC to EDT? We noticed that running a query for previous days transactions data resulted in the query ending at 8pm EDT because the query in AEP is in UTC.

[ccg1706]

Hi @RyanMoravick,

Try with the mySQL CONVERT_TZ function. Sometimes it does not work cause it goes a little crazy, you can do it manually by substracting the differences between timezones. I share with you the two screenshots of the syntax.

Some documentation regarding the function:

MySQL :: MySQL 8.4 Reference Manual :: 14.7 Date and Time Functions

Hope it is useful, and if you need something more just let me know.

Regards, 

Celia