Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Date Function on Analytic Fails to Return on First of Month

rebeccajenkins
New Contributor
New Contributor

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? 

3 REPLIES 3

Darshanjain
Saviynt Employee
Saviynt Employee

Hi @rebeccajenkins 

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

 

Thanks

Darshan 

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:

rebeccajenkins_0-1681478500206.png

Is that not a supported command after all? 

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