Click here to Skip to main content
15,565,474 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table which have Duration as a column which contains Sum of Minutes and i want to convert all the Duration it in to HH:MM Format.

ECodeDuration
101186
1021446
1031116


The output should be like this:-

ECodeDuration
1013.06
10224.06
10318.36


I want to do it with an Update statement and want to update all the rows like this .
Looking for the Query which may slve my problem.Thanks in advance.
Posted
Comments
Richard MacCutchan 1-Mar-12 7:25am    
Why? You should only need to do this conversion when you want to display the data.
Dharmenrda Kumar Singh 1-Mar-12 7:30am    
this data table will be loaded in to an excel format as a report for total production details and that's why i m looking for it.

 
Share this answer
 
Comments
bluesathish 1-Mar-12 8:13am    
good, Get 5 thatraja!
Hi there...

try this solution,

SQL
UPDATE tbFormat
SET Duration =
              (CASE
                   WHEN
                       ((Duration%60)<10)
                   THEN
                       CONVERT(VARCHAR(10),Duration/60)+':'+ CASE WHEN CONVERT(VARCHAR(10),Duration%60)<10 THEN +'0'+CONVERT(VARCHAR(10),Duration%60) END
                   ELSE
                       CONVERT(VARCHAR(10),Duration/60)+':'+CONVERT(VARCHAR(10),Duration%60)
                   END
               )



hope this helps..

plz revert back with ur comments...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900