Convert varchar to double. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert varchar to double.

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-15 : 03:20:59

I have the following fields for my db.

Userid, Date, TimeSpent
1,14/7/2009,1.5 Hours
1,14/7/2009,2.0 Hours
1,14/7/2009 4.5 Hours

My result will be come like the below.

UserId,Date,Total Time
1,14/7/2009,8.0 Hours

So i write the following query. i met error.

SELECT Date, UserId,
Sum(Cast(REPLACE(TimeSpent,'Hours','') As Double)) As 'Total time' FROM ITWP_TimeLogSheet GROUP BY Date,UserId, TimeSpent

kindly give your suggestions.

kamal.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 03:22:33

what's the error message ?

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-15 : 03:34:07

CREATE TABLE #TEMP (USER_ID INT, DATE DATETIME, HOURS VARCHAR(20))

INSERT INTO #TEMP
SELECT 1, '7/14/2009','1.5 HOURS' UNION ALL
SELECT 1,'7/14/2009','2.0 HOURS' UNION ALL
SELECT 1,'7/14/2009','4.5 HOURS'

SELECT [USER_ID],DATE,SUM(CAST(REPLACE(HOURS,'HOURS','')AS DECIMAL(3,2))) FROM #TEMP
GROUP BY DATE,[USER_ID]

-------------------------
R..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 03:39:59

DOUBLE is not a recognized SQL Server datatype.

N 56°04'39.26"
E 12°55'05.63"

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-15 : 04:18:22

Thanks, i have changed Double to Decimal, it is working fine. thank you

kamal.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 05:49:21