CODEWITHSUNDEEP
sql-server
Dharmendra
Dharmendra

Reputation: 217

Decimal to HH:MM Conversion

I have a table and I want to update the Duration column which of datatype Decimal to HH:MM format.

ECode        Duration
101           101.75
101           69.56
102           54.60
103           54.97

The output should be like this:

ECode        Duration
101           102.15
101           69.56
102           55
103           55.37

We are calculating the time after the decimal and if after decimal 60 or more then 60 will be there we are adding 1 before decimal and remaining after subtraction from 60 we are showing as it is.

Example 101.75, here after decimal 75 is there so firstly we check it is greater than or equal to 60. If yes then subtract 60 and add 1 before decimal and put the remaining 15 after decimal. So the result should be 102.15.

Please share query if possible to solve this issue.

Upvotes: 1

Views: 741

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

update YourTable set
  Duration =  floor(Duration) + 
              cast(((Duration - floor(Duration))*100) as int) / 60 +
              cast((cast((Duration - floor(Duration)) * 100 as int) % 60) as float) / 100

Upvotes: 2

Ral Zarek
Ral Zarek

Reputation: 1076

This should do it: 

update thistable 
set    duration = duration + 0.4 
where  duration - floor(duration) >= 0.6

Upvotes: 3

Svend Hansen
Svend Hansen

Reputation: 3333

I guess you would do something like ("pseudo" code of some sort):

String reorder(String x) {
  String parts[] = x.split(.);
  if(parts.length < 2) x;
  // eval as integers:
  if(parts[1] < 60) return x;
  if(parts[1] > 60) return (parts[0]+(parts[1]/60))+"."+(parts[1]%60);
  return parts[0]+1;
}

But exactly how you do it depends whether you're doing in a script, some application and the language you're using...

Upvotes: 0

Related Questions