Asked by:
Ticks into DD:HH:MM

-
So far i have this
CAST((SUM(sh.WorkedElapsedTicks) / 600000000)/60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM(sh.WorkedElapsedTicks) / 600000000) % 60 AS VARCHAR(8)), 2) as TimeSpent
this displays for example
1:15
how can i get this to display anything over 24 hours adds a day so it can be
01:25:54 (DD:HH:MM) also i'm trying to get the .2 decimals in each one so far decimals only work on the minutes.
Question
All replies
-
So far i have this
CAST((SUM(sh.WorkedElapsedTicks) / 600000000)/60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM(sh.WorkedElapsedTicks) / 600000000) % 60 AS VARCHAR(8)), 2) as TimeSpent
this displays for example
1:15
how can i get this to display anything over 24 hours adds a day so it can be
01:25:54 (DD:HH:MM) also i'm trying to get the .2 decimals in each one so far decimals only work on the minutes.- Merged by tracycaiMicrosoft contingent staff Thursday, February 20, 2014 8:21 AM duplicate
-
Hi
Use CONVERT function which is used to get whatever format we want. Try to include the CONVERT in your script like below,
CONVERT(VARCHAR, CAST((SUM(sh.WorkedElapsedTicks) / 600000000)/60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM(sh.WorkedElapsedTicks) / 600000000) % 60 AS VARCHAR(8)), 2), 108) as TimeSpent . let me know if any issue with this.
Mark it as answer.
Thanks
Hari
-
Hi
Use CONVERT function which is used to get whatever format we want. Try to include the CONVERT in your script like below,
CONVERT(VARCHAR, CAST((SUM(sh.WorkedElapsedTicks) / 600000000)/60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM(sh.WorkedElapsedTicks) / 600000000) % 60 AS VARCHAR(8)), 2), 108) as TimeSpent . let me know if any issue with this.
Mark it as answer.
Thanks
Hari
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, February 20, 2014 2:30 AM
-
Hello,
This brings back 29:34 for one day when it should be 01:05:34
Hi
Use CONVERT function which is used to get whatever format we want. Try to include the CONVERT in your script like below,
CONVERT(VARCHAR, CAST((SUM(sh.WorkedElapsedTicks) / 600000000)/60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM(sh.WorkedElapsedTicks) / 600000000) % 60 AS VARCHAR(8)), 2), 108) as TimeSpent . let me know if any issue with this.
Mark it as answer.
Thanks
Hari