Varchar Issue - 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

Varchar Issue

Author

Topic

jpistolero
Starting Member

6 Posts

Posted - 2009-01-21 : 17:11:10

I have a field in my database that stores the completion time of items in the following fashion...hh:mm:ss. The field has a type of varchar (20). I need to convert this time to decimal so I would like to multiply by 24. Unfortunately, it won't let me because of the data type. Basically if it had a completion time of 21:9:55, I would display the time as 21.17. Is there a way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 17:17:19

SELECT DATENAME(HOUR, col1) + '.' + LTRIM(STR(DATEDIFF(SECOND, 0, col1) % 3600) / 36.0E, 12, 0))
FROM Table1

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

jpistolero
Starting Member

6 Posts

Posted - 2009-01-21 : 17:25:26

Thanks for the quick repsonse! Sorry for the dumb question but what would col1 be? I try using this and it comes back saying col1 not part of query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 17:28:43

col1 is the name of the column used in your environment.
You didn't tell us so I just used any name for the column.

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

jpistolero
Starting Member

6 Posts

Posted - 2009-01-21 : 17:45:27

I am absolute idiot because it still doesn't run for me. This is what I have.

DATENAME(HOUR, ELAPSETIME) + '.' + LTRIM(STR(DATEDIFF(SECOND, 0, ELAPSETIME) % 3600) / 36.0E, 12, 0)) as DecimalTime

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 17:46:51

Post the entire query that you used plus the exact error message.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jpistolero
Starting Member

6 Posts

Posted - 2009-01-21 : 17:56:53

Thank you very much guys. This is going to be a big win for me if I can get this working. It gives me an error..."'E' not recognized".

SELECT TOP 100 PERCENT WO_NUM, COMPLETED, ELAPSETIME, PRIORITY,Datename(HOUR, ELAPSETIME) + '.' + LTRIM(STR(DATEDIFF(SECOND, 0, ELAPSETIME) % 3600) / 36.0E, 12, 0)) as DecimalTime
FROM TASKS
GROUP BY WO_NUM, COMPLETED, ELAPSETIME, PRIORITY
HAVING (COMPLETED BETWEEN @StartDate AND @EndDate)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 18:00:48

It must be having an issue with "36.0E". I'm not sure what Peter is doing in that calculation, so I'll let him answer why you are getting that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 18:06:13

SELECT DATENAME(HOUR, CAST(col1 AS DATETIME)) + '.' + LTRIM(STR(DATEDIFF(SECOND, 0, CAST(col1 AS DATETIME)) % 3600) / 36.0E, 12, 0))

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

jpistolero
Starting Member

6 Posts

Posted - 2009-01-21 : 18:13:02

That doesn't do it either. Error says "ltrim requires at least 1 argument".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 18:55:56

SELECT DATENAME(HOUR, CAST(col1 AS DATETIME)) + '.' + LTRIM(STR((DATEDIFF(SECOND, 0, CAST(col1 AS DATETIME)) % 3600) / 36.0E, 12, 0))

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

jpistolero
Starting Member

6 Posts

Posted - 2009-01-22 : 17:44:20

Peso,

Thanks for the help! The query works great. Would there be a way to store this in a number format so I can do calculations off it in reporting services?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 01:41:42

SELECT CAST(DATENAME(HOUR, CAST(col1 AS DATETIME)) + '.' + LTRIM(STR((DATEDIFF(SECOND, 0, CAST(col1 AS DATETIME)) % 3600) / 36.0E, 12, 0)) AS MONEY)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources