Ticks into DD:HH:MM

All replies

• 

  

  0

  Sign in to vote

  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

  Wednesday, February 19, 2014 12:48 PM

  Reply

  |

  Quote
• 

  

  0

  Sign in to vote

  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

  Reply

  |

  Quote
• 

  

  0

  Sign in to vote

  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

  Wednesday, February 19, 2014 1:14 PM

  Reply

  |

  Quote
• 

  

  0

  Sign in to vote

  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

  Reply

  |

  Quote