如何从分钟获得hh:mm格式的时间 [英] How to get time in hh:mm format from minutes
问题描述
我正在使用dbo.MinutesToDuration函数,但它返回
2:7
但是我想要那样
02:07
提前致谢!
我尝试了什么:
ALTER FUNCTION [dbo]。[MinutesToDuration]
(
@minutes int
)
RETURNS nvarchar(30)
AS
BEGIN
声明@hours nvarchar(20)
SET @hours =
CASE WHEN @minutes> = 60 THEN
(SELECT CAST((@ minutes / 60)AS VARCHAR(10))+':'+ - +'h' +
CASE WHEN(@minutes%60)> 0 THEN
CAST((@分钟%60)AS VARCHAR(10)) - +'m'
ELSE
''
END)
ELSE
CAST((@分钟%60)AS VARCHAR(10)) - +'m'
END
返回@hours
结束
SELECT d bo.MinutesToDuration(127)AS TIME
我们不知道你的MinutesTODuration函数做了什么 - 它不是标准的SQL函数,所以你需要开始看那个。
然而,当我使用TIME变量时,我得到了我期望的结果:
DECLARE @ st TIME ( 0 )= ' 02:07'
SELECT @ st AS TIME给予:
TIME
02:07: 00
DECLARE @ st < span class =code-keyword> TIME ( 0 )= ' 2:7'
SELECT @ st AS TIME
所以您需要查看正在使用格式化TIME值的表示层执行的操作。
好的,所以你向我们展示了你的函数 - 它没有返回TIME,它返回一个字符串......你专门用非前导零值填充...
假设您使用SQL 2012或更高版本:
SET @hours =
CASE WHEN @minutes> = 60 THEN
(选择格式(@minutes / 60,'d2')+
CASE WHEN(@minutes%60)> 0那么
':'+格式(@minutes%60,'d2')
END)
ELSE
格式(@minutes%60,'d2')
结束
如果您使用的是早期版本,则需要将FORMAT替换为:
SELECT RIGHT(' 00'+ CAST(@minutes / 60 AS NVARCHAR(5)),2)其余部分类似。
[/ EDIT]
但是,我试过添加'0'并且不更改代码逻辑。
请告诉我,如果这会简化你的问题!
声明
@minutes int = 301
--RETURNS nvarchar(30)
--AS
BEGIN
声明@hours nvarchar(20)
SET @hours =
CASE WHEN @分钟> = 60那么
(选择右('0'+ CAST((@分钟/ 60)AS VARCHAR(10)),2)+':'+ - +'h'+
CASE WHEN(@minutes%60)> 0 THEN
右('0'+ CAST((@分钟%60)AS VARCHAR(10)),2) - +'m'
ELSE
'00'
END)
ELSE
CAST((@分钟%60)AS VARCHAR(10)) - +'m'
结束
PS:如果你需要增加超过100的小时范围,那么只需添加3而不是2
VARCHAR(10)),2)
I am using dbo.MinutesToDuration function but its returning
2:7
But I want like that
02:07
Thanks in advance !
What I have tried:
ALTER FUNCTION [dbo].[MinutesToDuration] ( @minutes int ) RETURNS nvarchar(30) AS BEGIN declare @hours nvarchar(20) SET @hours = CASE WHEN @minutes >= 60 THEN (SELECT CAST((@minutes / 60) AS VARCHAR(10)) + ':' + --+ 'h' + CASE WHEN (@minutes % 60) > 0 THEN CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm' ELSE '' END) ELSE CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm' END return @hours END
SELECT dbo.MinutesToDuration(127) AS TIME
We don't know what your MinutesTODuration function does - it's not a standard SQL function, so you need to start looking at that.
However, when I use a TIME variable, I get what I expect:
DECLARE @st TIME(0) = '02:07' SELECT @st AS TIMEGives:
TIME 02:07:00As does
DECLARE @st TIME(0) = '2:7' SELECT @st AS TIME
So you need to look at what you are doing with your presentation layer that is formatting the TIME value.
[EDIT]
OK, so you showed us your function - which doesn't return a TIME, it returns a string ... which you specifically fill with non-leading-zero values ...
Assuming you use SQL 2012 or later:
SET @hours = CASE WHEN @minutes >= 60 THEN (SELECT FORMAT(@minutes / 60, 'd2') + CASE WHEN (@minutes % 60) > 0 THEN ':' + FORMAT(@minutes % 60, 'd2') END) ELSE FORMAT(@minutes % 60, 'd2') END
If you use an earlier version then the FORMAT needs to be replaced with:
SELECT RIGHT('00' + CAST(@minutes / 60 AS NVARCHAR(5)), 2)And similar for the remainder.
[/EDIT]
However, i have tried this by adding '0' and not changing the code logic.
Please let me know,if this would simplifies your problem!
declare @minutes int=301 --RETURNS nvarchar(30) --AS BEGIN declare @hours nvarchar(20) SET @hours = CASE WHEN @minutes >= 60 THEN (SELECT RIGHT ('0'+ CAST((@minutes / 60) AS VARCHAR(10)),2) + ':' + --+ 'h' + CASE WHEN (@minutes % 60) > 0 THEN RIGHT('0' + CAST((@minutes % 60) AS VARCHAR(10)),2) --+ 'm' ELSE '00' END) ELSE CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm' END
PS: If you need to increase the hours range more than 100 then simply add 3 instead of 2
VARCHAR(10)),2)
这篇关于如何从分钟获得hh:mm格式的时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!