CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Emel Uras
Emel Uras

Reputation: 404

Minutes to hh:mm format in SQL Server 2008 query

I have a column Time in my table. It holds a time value in minutes, ie: 605. What I want it to show it as 10:05, as hh:mm format.

I am doing it like this:

...
Time = cast(AVG(Time) / 60 as varchar) + ':' + cast(AVG(Time) % 60 as varchar),
...

It shows the value as 10:5, but I want to make it as 10:05 and I couldn't find out a way to add '0' before '5' in the minutes section.

Any help would be appreciated.

Upvotes: 1

Views: 87

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try this:

SELECT 
  TIME =  CASE WHEN @t < 600 THEN '0' ELSE '' END + 
  CAST(Time / 60 as varchar(10)) + ':' +RIGHT(100 +(Time % 60), 2)

Example:

DECLARE @t int = 55121

SELECT 
  TIME =  CASE WHEN @t < 600 THEN '0' ELSE '' END + 
          CAST(@t / 60 as varchar(10)) + ':' +RIGHT(100 +(@t % 60), 2)

Result:

Time
918:41

Upvotes: 2

rdbradshaw
rdbradshaw

Reputation: 223

You could simply add a Right('0' + ...,2) expression as below to get the '0' in there.

Time = cast(AVG(Time) / 60 as varchar) + ':' + RIGHT('0'+cast(AVG(Time) % 60 as varchar),2)

To answer your question in comments, getting DD:HH:MM is a similar setup.

DayTime = cast(AVG(Time) / 1440 as varchar) + ':' + RIGHT('0' + cast((AVG(Time) / 60) % 24 as varchar),2) + ':' + RIGHT('0'+cast(AVG(Time) % 60 as varchar),2)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a case expression to do this.

Also you should always specify the length of varchar when you use cast, or it would only show the first character.

case when len(cast(AVG(Time)%60 as varchar(2))) < 2 then '0'+cast(AVG(Time)%60 as varchar(2))
else cast(AVG(Time)%60 as varchar(2))
end

Upvotes: 0

Related Questions