如何将字符串转换为hr&在sql中的min [英] how to convert string to hr & min in sql
本文介绍了如何将字符串转换为hr&在sql中的min的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我在sql server 2008 r2中有表,其中我只需要存储hh:mm,为此
i已经将nvarchar(50)作为数据类型。
i尝试了这个,但我只是在寻找hh:mm only ,因为它转换成天也
声明@tb表(选择时间)
插入@tb(opt)值('15:10' )
插入@tb(opt)值('15:10')
插入@tb(opt)值('15:10')
从@tb
$ b $中选择DATEADD(ms,SUM(DATEDIFF(ms,'00:00:00.000',opt)),'00:00:00'000'作为'total_time'
b结果:1900-01-02 21:30:00.000
//结果我正在寻找'45:30'。
如何将此字符串转换为hh:mm然后如何才能获得此总和。
任何人都可以帮助我....
谢谢。
解决方案
不要使用字符串列来存储时间价值 - 使用时间
类型 [ ^ ]而是。
转换失败错误表明您的列包含不有效时间值或包含前导空格的值。您提供的两个示例值(15:10
和06:30
)都可以正确转换为时间
类型。
但是,你不能使用SUM
与时间
类型汇总。相反,您需要将每个实例表示的总分钟数相加:
DECLARE @ tb 表(选择时间);
INSERT INTO @ tb (选项)
VALUES (' 15:10'),(' 06:30'),(< span class =code-string>' 16:07');
SELECT
总和(DateDiff(分钟, 0 ,选项))作为 [TotalMinutes],
转换( varchar ( 5 ),Sum(DateDiff(分钟, 0 ,选项))/ 60 )
+ ' 小时'
+ 转换( char ( 2 ),Sum(DateDiff(分钟, 0 ,选项))% 60 )
+ ' minutes'
作为 [描述]
FROM
@ tb
;
选择 转换( time ,' 10:15', 0 )
请检查:
DECLARE @ tmp TABLE (TimeText NVARCHAR ( 50 ))
INSERT INTO @ tmp (TimeText)
VALUES (' 15:10'),(' 06:30'),(' < span class =code-string> 16:07')
SELECT CONVERT ( TIME ,CONCAT(TimeText, ' :00.000')) AS MyTime
FROM @ tmp
为了能够获得小时和分钟的总和,请使用以下内容:
; WITH SumOfTime AS
(
SELECT CONVERT ( TIME ,CONCAT(TimeText,' :00.000')) AS MyTime,DATEPART(HOUR, CONVERT ( TIME ,CONCAT(TimeText,' :00.000'))) AS 小时,DATEPART(MINUTE, CONVERT ( TIME ,CONCAT(TimeText,' :00.000' ))) AS mns
FROM @tmp
)
SELECT SUM(小时) AS TotalHrs,SUM(mns) AS TotalMns
FROM SumOfTime
Hi guys,
I have table in sql server 2008 r2, in which i have to store hh:mm only, for that
i have taken nvarchar(50) as datatype.
i have tried this, but i'm just looking for hh:mm only, where as it is converting into days as well
Declare @tb table(opt time) insert into @tb(opt) values('15:10') insert into @tb(opt) values('15:10') insert into @tb(opt) values('15:10') select DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', opt)), '00:00:00.000') as 'total_time' from @tb Result: 1900-01-02 21:30:00.000 //the result i'm looking for it as '45:30'.
how can i convert this string to hh:mm then how can i get sum of this.
Can anyone please help me....
Thanks.
解决方案
Don't use a string column to store a time value - use thetime
type[^] instead.
The "conversion failed" error suggests that your column contains a value which is not a valid time value, or which contains leading white-space. Both example values you've provided (15:10
and06:30
) can be correctly converted to thetime
type.
However, you can't use theSUM
aggregate with thetime
type. Instead, you'll need to sum the total minutes represented by each instance:
DECLARE @tb TABLE (Opt time); INSERT INTO @tb (Opt) VALUES('15:10'), ('06:30'), ('16:07'); SELECT Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes], Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60) + ' hours ' + Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60) + ' minutes' As [Description] FROM @tb ;
select convert(time,'10:15', 0)
Please, check this:
DECLARE @tmp TABLE (TimeText NVARCHAR(50)) INSERT INTO @tmp (TimeText) VALUES('15:10'), ('06:30'), ('16:07') SELECT CONVERT(TIME, CONCAT(TimeText, ':00.000')) AS MyTime FROM @tmp
To be able to get sum of hours and minutes, use something like this:
;WITH SumOfTime AS ( SELECT CONVERT(TIME, CONCAT(TimeText, ':00.000')) AS MyTime, DATEPART(HOUR, CONVERT(TIME, CONCAT(TimeText, ':00.000'))) AS hrs, DATEPART(MINUTE,CONVERT(TIME, CONCAT(TimeText, ':00.000'))) AS mns FROM @tmp ) SELECT SUM(hrs) AS TotalHrs, SUM(mns) AS TotalMns FROM SumOfTime
这篇关于如何将字符串转换为hr&在sql中的min的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文