How to put a date and time condition in a ADQL statement

[Tausif.Ahmad]

How can I put the current date in the where clause? For example with the below query I want to fetch all IDOCs that has been created today. I have just hard coded today's date. What should I use to put the today's date condition?

SELECT CREDAT, DOCNUM, STATUS, MESTYP, TIMESTAMP FROM idocs_details WHERE MESTYP = "ZPSWDMGMT" AND CREDAT = "20220324" anD STATUS = "51"

[Kenji.Kumada]

Hi @Tausif.Ahmad

Thank you for your post to the community!

If your environment is a Saas environment, one option is to use "now" function.
https://docs.appdynamics.com/appd/21.x/21.5/en/analytics/adql-reference/adql-queries/analytics-funct...

This function returns a datetime value. From this value, you can create another datetime value for the current date at 00:00, and use it in "WHERE" clause. This is an example:

SELECT eventTimestamp, application, toString(eventTimestamp, 'yyyyMMdd-HH:mm') AS eventTimestampString, toString(now(), 'yyyyMMdd') AS currentDateString, toDate(currentDateString, 'yyyyMMdd') AS currentDate FROM transactions WHERE (eventTimestamp - currentDate) > 0 LIMIT 100

Note that comparing two datetime fields or comparing a datetime field and a returned value by "now" function may not work. But you can use the result of subtraction for a condition as in the example.

If your environment is not Saas, unfortunately, there is no straightforward way to do this. It may be possible to use "SINCE ... UNTIL" clause or "series" function but it depends on the data. And probably it may require some data manipulation. Here is the documentation for "SINCE ... UNTIL" clause and "series" function.

https://docs.appdynamics.com/appd/21.x/21.1/en/analytics/adql-reference/adql-queries/since-until-cla...
https://docs.appdynamics.com/appd/21.x/21.5/en/analytics/adql-reference/adql-queries/analytics-funct...

Hope this answer helps!

Best regards,
Kenji