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

Sailaja
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,
userTable.email 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.

Thanks,
Sailaja.

3 REPLIES 3

SumathiSomala
All-Star
All-Star

@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

 

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

KevinP
New Contributor
New Contributor

@SumathiSomala 
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 Excel...it'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:

KevinP_0-1702653857797.png

But looks like this in Excel:

KevinP_1-1702653898390.png

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.  

KevinP_2-1702653944988.png

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.

 

 

 

 

 

Saathvik
All-Star
All-Star

@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,


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