SQL Server 2008查询中的分钟到hh：mm格式(Minutes to hh:mm format in SQL Server 2008 query)

SQL Server 2008查询中的分钟到hh：mm格式(Minutes to hh:mm format in SQL Server 2008 query)

我的表中有一列Time 。 它保持一个以分钟为单位的时间值，即：605。我想把它显示为10:05，如hh:mm格式。

我是这样做的：

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

它将值显示为10：5，但我想将其设为10:05，并且我找不到在分钟部分中在'5'之前添加'0'的方法。

任何帮助，将不胜感激。

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.

最满意答案

您可以使用case表达式来执行此操作。

此外，您应该始终在使用强制转换时指定varchar的长度，或者它只显示第一个字符。

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

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