Minutes to HH:MM Conversion

Question

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.

ECode	Duration
101	186
102	1446
103	1116

The output should be like this:-

ECode	Duration
101	3.06
102	24.06
103	18.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 1-Mar-12 0:22am

Dharmenrda Kumar Singh

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

rajdeep kumar · Answer 1 · 2012-03-01T18:51:00

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...

thatraja · Answer 2 · 2012-03-01T01:48:00

Solution 1

Google? :sigh:
Minutes to time in SQL Server[^]

Posted 1-Mar-12 1:48am

thatraja

Comments

bluesathish 1-Mar-12 8:13am

good, Get 5 thatraja!