Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Converting literal date values to Date Format

I have a new project that is pulling in data from a proprietary SQL database.  I am using a query to get the data and it comes through fine, however the date data is showing up as long integers like 1412956095860.  I tried to use the DATE() or DATEVALUE() to convert it to mm/dd/yyyy hh:ss, but it is not working.  When I save the results as a csv and then open in Excel, it seems to convert automatically.  What should I do to solve this?  Or should I just have a SQL script that produces a csv and then read the data from there into Power BI?

 

Thanks,

 

Brad

 

 

 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I think I found the issue: http://lawthirtysix.com/reporting-choices-and-date-handling-in-servicedesk-plus/ 

 

And if anyone else in the future is looking for a possible answer to this, in my SQL query for a date or time column, I used this: dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ("RESPONDEDTIME"/1000),'01-01-1970 00:00:00')  AS "Responded Date" 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

When you say proprietary SQL database do you mean not Microsoft or Oracle or IBM but something you wrote or some other vendor?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I think I found the issue: http://lawthirtysix.com/reporting-choices-and-date-handling-in-servicedesk-plus/ 

 

And if anyone else in the future is looking for a possible answer to this, in my SQL query for a date or time column, I used this: dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ("RESPONDEDTIME"/1000),'01-01-1970 00:00:00')  AS "Responded Date" 

Anonymous
Not applicable

Something written from a vendor; proprietary might not be the correct wording, but it is a database I am not going to change.  It is a local instance, but is intertwined with SaaS we use.  And I am thinking that all this number is is probably the number of seconds from 1-1-1900 (or whatever it is) so I need to divide that number by 86400 and format on that result?  These numbers I think are like the Excel Date serial numbers and might be an easy way for the program to do Date Differences quickly.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.