Click HERE to see how Saviynt Intelligence is transforming the industry. |
12/18/2023 06:20 AM
We have an emailed report with users' start date which of course comes via Excel instead of a CSV abd it's interpreting the date as a string of irrelevant numbers. Only if I open the Excel sheet, change the cell format to Date, does it reflect the date correctly. In Data Analyzer and while building the report, 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...soon as I take the extra y away it goes back to irrelevant numbers.
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. Any pointers on the proper date format to get this to show up correctly/natively in Excel?
Thanks,
Kevin
12/18/2023 06:31 AM
Try
DATE_FORMAT(u1.startdate, '%m-%d-%Y')
12/18/2023 06:45 AM
@SumathiSomala
Nope - whether I use single or double quotes in DATE_FORMAT(u1.startdate, '%m-%d-%Y'), the result is the same.
12/18/2023 07:10 AM
Can u please try:
FROM_UNIXTIME(u1.startdate, '%Y %D %M %h:%i:%s %x')
12/18/2023 07:42 AM
@Vinit556It doesn't appear to like that one, I put it in for datestart:
FROM_UNIXTIME(u1.startdate, '%Y %D %M %h:%i:%s %x') AS DATESTART
Which resulted in the below:
12/18/2023 08:54 AM
DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s')
12/18/2023 10:29 AM
Negatory - that made the start date a little weirder. Here is the what's in the select statement and the output: DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') AS DATESTART,
12/21/2023 01:29 PM
Nobody? I can't be the first person who just wanted to return a date into an Excel-ready format?
12/21/2023 02:07 PM - edited 12/21/2023 02:08 PM
Can you share your query?
Since it is functioning for me as planned
12/22/2023 05:05 AM
Hi @KevinP
Could you please share the complete query and where you are using it. It can be an issue with default date format in your excel since it is working for you as expected in the data analyzer. Can you please share the complete steps you have followed so that we can replicate this.
Regards,
Dhruv Sharma
01/03/2024 06:32 AM
Here is the full query - you can see I've been messing with the format of the starting date and end date trying a couple of different things now as a result of this forum.
Thanks!
select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') AS DATESTART, DATE_FORMAT(u1.enddate, '%m-%d-%Y') AS DATEEND, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'
01/03/2024 07:44 PM
Can you check configuration in global configuration -> Preference - Date Format
01/04/2024 12:18 AM
Hi @KevinP
Could you please refer this post and let us know if this helps.
Solved: formatting date in email template - Saviynt Forums - 64336
Regards,
Dhruv Sharma
01/04/2024 06:15 AM
@Dhruv_S @rushikeshvartak
I went to Global Settings -> Preferences and the Date/Time setting was blank so I set it to MM-DD-YYYY, though that doesn't seem to make a difference to the emailed report.
I also viewed the post that Dhruv mentioned, but it's the same thing that I've seen before where we are told to not have the column name end in 'date' which I've already been doing. You can see my full query now where I'm trying a bunch of different formats to see how they all come out.
Again - I'm not quite sure why DATE_FORMAT(u1.startdate, "%m-%d-%Y") doesn't work, while DATE_FORMAT(u1.startdate, "%m-%d-%Yy") gets me most of the way there with the extra 'y'.
select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, u1.startdate as DateStart1, DATE_FORMAT(u1.startdate, '%Y-%m-%d %H:%i:%s') AS DATESTART2, DATE_FORMAT(u1.startdate, '%m-%d-%Y') AS DATEStart3, DATE_FORMAT(u1.startdate, "%b %d, %Y") AS DateStart4, DATE_FORMAT(u1.startdate, "%m-%d-%Y") AS DateStart5, DATE_FORMAT(u1.startdate, "%m-%d-%Yy") AS DateStart6, DATE_FORMAT(u1.startdate, "MM-DD-YYYY") AS DateStart7, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'
01/07/2024 11:14 PM - edited 01/07/2024 11:16 PM
Hi @KevinP
Thanks for your patience and co-operation on this issue.
We have tested the same query in our environment and getting results as expected while trying to use the query in runtime analytics and downloading as excel report. The report gets downloaded as Excel 97-2003 Worksheet (attached sample here).
The results look like below in the report we have downloaded post replicating the above query.
DATESTART1 | DATESTART2 | DATESTART3 | DATESTART4 | DATESTART5 | DATESTART6 | DATESTART7 |
08-24-2023 00:00:00 | 2023-08-24 00:00:00 | 08-24-2023 | Aug 24, 2023 | 08-24-2023 | 08-24-2023y | MM-DD-YYYY |
09-05-2023 00:00:00 | 2023-09-05 00:00:00 | 09-05-2023 | Sep 05, 2023 | 09-05-2023 | 09-05-2023y | MM-DD-YYYY |
09-22-2023 00:00:00 | 2023-09-22 00:00:00 | 09-22-2023 | Sep 22, 2023 | 09-22-2023 | 09-22-2023y | MM-DD-YYYY |
09-29-2023 00:00:00 | 2023-09-29 00:00:00 | 09-29-2023 | Sep 29, 2023 | 09-29-2023 | 09-29-2023y | MM-DD-YYYY |
11-23-2023 00:00:00 | 2023-11-23 00:00:00 | 11-23-2023 | Nov 23, 2023 | 11-23-2023 | 11-23-2023y | MM-DD-YYYY |
11-23-2023 00:00:00 | 2023-11-23 00:00:00 | 11-23-2023 | Nov 23, 2023 | 11-23-2023 | 11-23-2023y | MM-DD-YYYY |
As confirmed by Engineering teams internally there has been no recent changes/enhancements related to the date formatting in any of the recent versions.
Could you please check with your Microsoft excel configurations if there is any setting which might be causing the date to appear in wrong format when you open the excel.
Regards,
Dhruv Sharma
01/12/2024 08:34 AM
Dhuv,
What's interesting is mine come as .xlsx (post-Excel 2003), not .xls like yours. Where is that controlled? Is there a setting somewhere to define the file extension/format it gets delivered as?
01/08/2024 06:42 PM
select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, u1.startdate as DateStart1, DATE_FORMAT(u1.startdate, '%Y-%m-%d %H:%i:%s') AS DATESTART2, DATE_FORMAT(u1.startdate, '%m-%d-%Y') AS DATEStart3, DATE_FORMAT(u1.startdate, "%b %d, %Y") AS DateStart4, DATE_FORMAT(u1.startdate, "%m-%d-%Y") AS DateStart5, DATE_FORMAT(u1.startdate, "%m-%d-%Y ") AS DateStart6, DATE_FORMAT(u1.startdate, "MM-DD-YYYY") AS DateStart7, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'
01/14/2024 07:43 PM
@KevinP Did you tried above query