Correct Syntax in AEP Query Service

Question

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 Answer

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

Sign up

Login with SSO

Login to the community

Login with SSO