none
Ticks into DD:HH:MM

    Question

  • 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.
    Wednesday, February 19, 2014 12:42 PM

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.
    Wednesday, February 19, 2014 12:48 PM
  • 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

    Wednesday, February 19, 2014 1:13 PM
  • 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

    Wednesday, February 19, 2014 1:14 PM
  • 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


    Wednesday, February 19, 2014 3:30 PM