conversion problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

conversion problem

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-01 : 10:31:57

Hi,
I have a table with two columns(Date,DutyTime).Both fields are in varchar.I have to get the sum(DutyTime) in hours(eg:if 01.30+0.35->02.05).How can I get the result using cast, convert or some other methods.Please help me.

Here is the result

Date
2009-12-30 00:00:00.000
2009-12-30 00:00:00.000
2009-12-30 00:00:00.000
2009-12-30 00:00:00.000
2009-12-31 00:00:00.000
2009-12-31 00:00:00.000
2009-12-31 00:00:00.000
2009-12-31 00:00:00.000

DutyTime
00:35
01:00
00:35
00:55
00:30
01:00
00:30
01:30

Thanks & Regards
Binto Thomas

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 10:43:31

"Both fields are in varchar"

Convert the columns to be DATETIME instead. Its a really bad idea storing Date and/or Time in varchar in the database. Before you can do anything with it you ahve to convert it - no telling if your data is actually valid datetime until you try to process it, performance is dreadful having to CAST it every time you use it, etc. etc. etc.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-01 : 12:37:21

I second what Kristen said..

Now, I'm wasn't sure what you wanted to do if the hours go over 24, or if that is even possible. So, here is some sample code that might help get you going.

DECLARE @T TABLE (DutyTime VARCHAR(20))

INSERT @T
SELECT '00:35'
UNION ALL SELECT '01:00'
UNION ALL SELECT '00:35'
UNION ALL SELECT '00:55'
UNION ALL SELECT '00:30'
UNION ALL SELECT '01:00'
UNION ALL SELECT '00:30'
UNION ALL SELECT '01:30'

SELECT 
	SUM(DATEPART(MINUTE, CAST('19000101 ' + DutyTime AS DATETIME))) AS TotalMinutes,
	DATEADD(MINUTE, SUM(DATEPART(MINUTE, CAST('19000101 ' + DutyTime AS DATETIME))), '19000101') AS NewDate,
	RIGHT(CONVERT(VARCHAR(16), DATEADD(MINUTE, SUM(DATEPART(MINUTE, CAST('19000101 ' + DutyTime AS DATETIME))), '19000101'), 121), 5) AS HoursMinutes
FROM @T

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 00:42:03

Lamprey,
Thanks a lot for helping me.The result I want to get is 'HoursMinutes' in your query.
From the above query I am getting 'HoursMinutes' as '03:35' ie Sum of minutes in Hours.But I want to get the total time in Hours.For example
01.00+00.30+01.35---> 03.05

Can you help me..?

Thanks & Regards
Binto Thomas

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-02 : 01:16:08

quote:
Originally posted by binto

Lamprey,
Thanks a lot for helping me.The result I want to get is 'HoursMinutes' in your query.
From the above query I am getting 'HoursMinutes' as '03:35' ie Sum of minutes in Hours.But I want to get the total time in Hours.For example
01.00+00.30+01.35---> 03.05

Can you help me..?

Thanks & Regards
Binto Thomas

try this


DECLARE @T TABLE (DutyTime VARCHAR(20))

INSERT @T
SELECT '00:35'
UNION ALL SELECT '01:00'
UNION ALL SELECT '00:35'
UNION ALL SELECT '00:55'
UNION ALL SELECT '00:30'
UNION ALL SELECT '01:00'
UNION ALL SELECT '00:30'
UNION ALL SELECT '01:30'

SELECT 
	CASE WHEN SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))%60 = 0 
THEN  
	RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))/60 AS VARCHAR(30)),2)+':00' 
ELSE RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))/60 AS VARCHAR(30)),2)+':'+RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))%60 AS VARCHAR(30)),2) END  
	AS HoursMinutes
FROM @T

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 02:01:31

Thanks a lot Raky.Its working fine.

Thanks & Regards
Binto Thomas

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-02 : 03:58:10

quote:
Originally posted by binto

Thanks a lot Raky.Its working fine.

Thanks & Regards
Binto Thomas

Welcome...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-02 : 11:05:12

Another alternative:

SELECT
	CAST(DATEPART(HOUR, HoursMinutes) AS NUMERIC(18,2)) + CAST((DATEPART(MINUTE, HoursMinutes)/ 60.0) AS NUMERIC(18,2))AS HoursMinutes,
	DATEPART(HOUR, HoursMinutes),
	DATEPART(MINUTE, HoursMinutes),
	DATEPART(MINUTE, HoursMinutes)/ 60.0
FROM
(
	SELECT 
		DATEADD(MINUTE, SUM(DATEPART(MINUTE, CAST('19000101 ' + DutyTime AS DATETIME))), '19000101') AS HoursMinutes
	FROM @T
) AS T

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 05:06:14

Thanks a lot Lamprey.Keep your Great work..!!!

Thanks & Regards
Binto Thomas

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 05:14:50

I have used the following
RIGHT(CONVERT(VARCHAR(16), DATEADD(MINUTE, SUM(DATEPART(HOUR, CAST('19000101 ' + DutyTime AS DATETIME)))*60 + SUM(DATEPART(MINUTE, CAST('19000101 ' + DutyTime AS DATETIME))), '19000101'), 121), 5) AS HoursMinutes

any filtered query in performance wise?.plz post

Thanks & Regards
Binto Thomas

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 06:04:25

"any filtered query in performance wise?"

The conversion of DutyTime to DATETIME via string cast is less than ideal. It would be better that DutyTime was in a DATETIME datatype to start with.

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 06:59:14

I do agree with Kristen.If I would change DutyTime as 'datetime',how can I get the result with
Hours:Minutes(1900-01-01 01:30:00.000 ---->> 01:30).And I want to get the sum of time of two dates
(ie 1900-01-01 01:30:00.000 + 1900-01-01 02:35:00.000 --> 04:05)
Plz help me
Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 07:03:11

quote:
Originally posted by binto

I do agree with Kristen.If I would change DutyTime as 'datetime',how can I get the result with
Hours:Minutes(1900-01-01 01:30:00.000 ---->> 01:30).Plz help

Thanks & Regards
Binto Thomas

you can apply formatting functions at your front end to do that
or if you want to do it in t-sql use convert

convert(varchar(8),yourdatecol,108)

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 10:25:41

quote:
Originally posted by raky

quote:
Originally posted by binto

Thanks a lot Raky.Its working fine.

Thanks & Regards
Binto Thomas

Welcome...

Raky,

If I have
DECLARE @T TABLE (DutyTime VARCHAR(20))

INSERT @T
SELECT '00:35'
UNION ALL SELECT '01:00'
UNION ALL SELECT '00:35'
UNION ALL SELECT '100:55'
UNION ALL SELECT '00:30'
UNION ALL SELECT '01:00'
UNION ALL SELECT '125:30'
UNION ALL SELECT '01:30'

how can I get the result..?Pls help me with your solution

SELECT
CASE WHEN SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))%60 = 0
THEN
RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))/60 AS VARCHAR(30)),2)+':00'
ELSE RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))/60 AS VARCHAR(30)),2)+':'+RIGHT('00'+CAST( SUM(LEFT(DutyTime,2)*60+RIGHT(DutyTime,2))%60 AS VARCHAR(30)),2) END
AS HoursMinutes
FROM @T

or please help me with another solution.

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 11:00:56

[code]SELECT CONVERT(varchar(10),SUM(PARSENAME(REPLACE(DutyTime,':','.'),2)*1) + (SUM(PARSENAME(REPLACE(DutyTime,':','.'),1)*1)/60)) + ':' + RIGHT('0' + CONVERT(varchar(2),(SUM(PARSENAME(REPLACE(DutyTime,':','.'),1)*1)%60)),2) FROM table[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources