Date Function on Analytic Fails to Return on First of Month

[rebeccajenkins]

Issue is with the CURRENT_DATE function where on the first day of the month, where the SQL uses CURRENT_DATE-1 to pull departures for the previous day. The data used in the WHERE clause shows as the 0 day of the month (i.e., 3/0 or 4/0). What DATE data functions does Saviynt support that can resolve this? 

[Darshanjain]

Hi @rebeccajenkins 

You can use DATEDIFF or DATEADD . Try this out as per your requirement

Thanks

Darshan 

[rebeccajenkins]

Thanks for the suggestion, Darshanjain. I was unable to return the expected results with something like "WHERE ENDDATE = DATE_FORMAT(DATEDIFF(CURRENT_DATE, CURRENT_DATE-1), '%Y-%m-%d')" and am getting this error in the Data Analyzer when attempting alternatives with DATEADD:

Is that not a supported command after all? 

[Darshanjain · Accepted solution]

Hi @rebeccajenkins 

The above syntax is incorrect , you can validate these in google. (datediff value comes in integers ), 

For your exact requirement you can use this in analytics

SELECT username FROM users WHERE ENDDATE = DATE(now() - INTERVAL 1 DAY);

Thanks

Darshan