如何在TSQL中将分钟数转换为hh:mm格式? [英] How to convert number of minutes to hh:mm format in TSQL?
本文介绍了如何在TSQL中将分钟数转换为hh:mm格式?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有一个包含DURATION列的select查询来计算Minutes的数量。我想将这些分钟转换为hh:mm格式。
持续时间的值为60,120,150
例如:
510变为08:5小时
i已尝试过以下查询,但未获得确切的值。
选择ISNULL(CONVERT(VARCHAR,CONVERT(INT,510)/ 60)+':'+ RIGHT('0'+ CONVERT(VARCHAR,CONVERT(INT,510)%60),2),0)AS TotalHour
我的预期出货量是:
8:5
>
请指导我,我犯了错误。
感谢所有。
问候,
stellus
我的尝试:
如何在TSQL中将分钟数转换为hh:mm格式?
解决方案
请参阅下面链接查询类似的问题,并有很好的答案。
sql server 2008 - 如何在TSQL中将分钟数转换为hh:mm格式? - 堆栈溢出 [ ^ ]
选择左(转换(时间,DATEADD(分钟,[column_name],0)),5)为[时间] ]来自[tbl_name]
DECLARE @ tm INT ;
SELECT @ tm = SUM(tm) FROM (
SELECT 60 AS tm UNION SELECT 120 UNION SELECT 150
) AS hm
SELECT 格式(DATEADD(MINUTE, @ tm ,CAST(CAST( 0 AS FLOAT ) AS DATETIME )), ' HH:mm');
Hi all,
I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format.
Duration has values like 60, 120,150
For example: 510 becomes 08:5 hours
i have tried below query but am not getting the exact value.
select ISNULL(CONVERT(VARCHAR,CONVERT(INT,510)/60)+':'+ RIGHT('0'+CONVERT(VARCHAR,CONVERT(INT,510)%60),2),0) AS TotalHour
my expected out put is:
8:5
please guide me were i made mistake.
thanks to all.
regards,
stellus
What I have tried:
How to convert number of minutes to hh:mm format in TSQL?
解决方案
Please see below link for a similar question asked and have good answers.
sql server 2008 - How to convert number of minutes to hh:mm format in TSQL? - Stack Overflow[^]
select left(convert(time,DATEADD(minute,[column_name],0)),5) as [Time] from [tbl_name]
DECLARE @tm INT; SELECT @tm =SUM(tm) FROM ( SELECT 60 AS tm UNION SELECT 120 UNION SELECT 150 )AS hm SELECT format(DATEADD(MINUTE,@tm,CAST(CAST(0 AS FLOAT) AS DATETIME)),'HH:mm');
这篇关于如何在TSQL中将分钟数转换为hh:mm格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文