How To Convert Milliseconds to Time (Day hh:mm:ss) in SQL and then in power BI?

[mgu098]

You might have come across a situation when SQL data which is in milliseconds is shown as below in Power BI desktop as whole number and you want to convert it in Days HH : MM : SS

Please use below query to convert for example

"TOP (100) PERCENT
dbo.service_req.timer1,
FLOOR(dbo.service_req.timer1 / (1000 * 60 * 60 * 24)) AS Timetocloseindays, FLOOR(dbo.service_req.timer1 / (1000 * 60 * 60)) % 24 AS Timetocloseinhours,
FLOOR(dbo.service_req.timer1 / (1000 * 60)) % 60 AS Timetocloseinminutes, FLOOR(dbo.service_req.timer2 / (1000 * 60 * 60 * 24)) AS Timetorespondindays,
FLOOR(dbo.service_req.timer1 / (1000 * 60 * 60)) % 24 AS Timetorespondininhours, FLOOR(dbo.service_req.timer1 / (1000 * 60))
% 60 AS Timetorespondininminutes"

Output will be as below

[v-qiuyu-msft]

Hi @mgu098,

Thnaks for your sharing.  

Best Regards,

Qiuyun Yu