how to convert string to hr & min in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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.

Posted 1-Dec-14 3:52am

abdul subhan mohammed

Updated 1-Dec-14 4:23am

v7

Add a Solution

3 solutions

Solution 1

SQL

select convert(time,'10:15', 0)

Posted 1-Dec-14 3:58am

Kornfeld Eliyahu Peter

Comments

abdul subhan mohammed 1-Dec-14 10:02am

Error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Kornfeld Eliyahu Peter 1-Dec-14 10:04am

What is your default collation in SQL?
You may pick an other format code instead of 0 - check here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Solution 3

Please, check this:

SQL

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:

SQL

;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

Posted 1-Dec-14 4:12am

Maciej Los

Updated 1-Dec-14 4:22am

v2

Comments

abdul subhan mohammed 1-Dec-14 10:26am

dude i'm getting error:

CONCAT is not part of built in function

i'm using SQL SERVER 2008 R2

Maciej Los 1-Dec-14 10:29am

So, use CONVERT(NVARCHAR(50), TimeText + ':00.000')

Richard Deeming 1-Dec-14 10:28am

Summing hours and minutes separately isn't going to produce a sensible answer - what happens when the total minutes exceeds 60?

Maciej Los 1-Dec-14 10:32am

Nothing ;) There is an area for deliberation...
If total minutes exceeds 60, it can be devided by 60 and the result should be added to the total hours ;)
That's all.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-12-01T04:04:00

Solution 2

Don't use a string column to store a time value - use the time 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 and 06:30) can be correctly converted to the time type.

However, you can't use the SUM aggregate with the time type. Instead, you'll need to sum the total minutes represented by each instance:

SQL

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
;

Posted 1-Dec-14 4:04am

Richard Deeming

Updated 1-Dec-14 4:34am

v2

Comments

abdul subhan mohammed 1-Dec-14 10:11am

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 * from @tb --got the values

select sum(opt) from @tb -- got the error

Msg 8117, Level 16, State 1, Line 6
Operand data type time is invalid for sum operator.

Richard Deeming 1-Dec-14 10:35am

You can't use SUM with time values. You'll need to sum the total minutes of each value instead. I've updated my answer with an example.

abdul subhan mohammed 1-Dec-14 13:39pm

please check this link:
http://www.codeproject.com/Questions/848617/sum-of-time-in-sql-server-r

Maciej Los 1-Dec-14 10:24am

Good advice to use proper data type. +5!
As per my understanding, OP want to sum time, but He can't use aggregate function. Please, see my answer.

abdul subhan mohammed 1-Dec-14 11:24am

Thank you v..v.v.v..v...much Richard, thanks again.

abdul subhan mohammed 1-Dec-14 13:39pm

please check this link:
http://www.codeproject.com/Questions/848617/sum-of-time-in-sql-server-r