Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Issue when using DateFormat in SQL query through Analytics

New Contributor
New Contributor

Hello all,

I am currently running a query through analytics which gives me the details of users with start date and end date included as the fields in it. When I used the DATEFORMAT function running through analytics gives me issue. I see a dialog box mentioning that "it is taken more time to run and will be notified once completed" but the output never comes.
Below is my query :

select userTable.username AS USERNAME,
userTable.firstname AS FIRST_NAME,
userTable.lastname AS LAST_NAME, AS EMAIL,
DATE_FORMAT(userTable.startdate, "%%mm-%dd-%yyyy") AS STARTDATE,
DATE_FORMAT(userTable.enddate, "%%mm-%dd-%yyyy") AS ENDDATE,
managerUserTable.username AS USR_MANAGER,
customerTable.customername AS PRIMARY_ORG from USERS userTable
left join Users managerUserTable
on userTable.manager = managerUserTable.userkey
left join customer customerTable
on userTable.customer = customerTable.customerkey

The query is running fine through the DATA Analyser and gives the expected output for first few records.
Wanted to understand why this is happening , please let me know if there is any resolution to this.




@Sailaja did you check this knowledge article

Standard Report Queries - Saviynt Forums - 50282

Solved: date format in analytics report is throwing error - Saviynt Forums - 40477


Sumathi Somala
If this reply answered your question, please Accept As Solution and give Kudos.

New Contributor
New Contributor

I have a similar issue related to this but not exactly.  I'm doing something similar to the above and the Excel sheet gets delivered via email just fine - but since it's's interpreting the date as a string of numbers.  Only if I open the Excel sheet, change the cell format to Date, does it reflect the date correctly.  As mentioned above, in Data Analyzer the date looks perfect; only when its exported to an Excel attachment does it show up wrong.  

So I've been trying to play with DATE_FORMAT and CAST or CONVERT (to a string) to get it to spit out the correct date format to Excel....but I've spent a lot of time doing trial and error.  Do you have a proper method to do this?  

I currently have it set like this in my select statement: DATE_FORMAT(u1.startdate, "%m-%d-%Y")
Which looks fine in Data Analyzer:


But looks like this in Excel:


I've been playing with different formats, and the closest I got was using this:
DATE_FORMAT(u1.startdate, "%m-%d-%Yy")
Which comes out like this in Excel with that extra 'y' in it of course.  


This is probably something really simple for someone who does this every day and I'm sure I'm missing one tiny piece that I don't understand.







@Sailaja : Change the start date, end date columns as below and try. Columns ending with date as alias is not allowed.

DATE_FORMAT(userTable.startdate, "%m-%d-%Y") AS DATESTART,
DATE_FORMAT(userTable.enddate, "%m-%d-%Y") AS DATEEND,

If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.