Converting Date calculation result to decimal

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

Converting Date calculation result to decimal

Author

Topic

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-03 : 05:32:17

Hi there,

I have a query like this:

"Select TotalHours_Worked =
convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108)"

Which gives me a result that looks like this : "10:30:47"

I would like to convert this result to a decimal, so that I get a result that looks like this : "10.5"

Can anyone help me get this?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-03 : 05:44:42

SELECT CAST(DATEPART(MI,GETDATE()) AS VARCHAR(2))+':'+CAST(DATEPART(S,GETDATE()) AS VARCHAR(2)) AS StrTime
,CAST(DATEPART(MI,GETDATE()) AS VARCHAR(2))+'.'+CAST(DATEPART(S,GETDATE())*100/60 AS VARCHAR(2)) AS StrFloat

--------------------------
http://connectsql.blogspot.com/

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-03 : 06:07:32

I am calculating the difference between dates, (ClockIn, and ClockOut) what is "GetDate()"?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-03 : 06:09:06

Its just a sample query, to show you, how can you achive your goal. GETDATE() is used to get current system datetime

--------------------------
http://connectsql.blogspot.com/

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-03 : 06:12:51

Ok, but how would I corporate the calculation between ClockIn and ClockOut? DatePart only take in 1 argument, I need a datediff.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-03 : 06:13:36

SELECT CAST(DATEPART(HH,
DATEADD(second, SUM(DATEDIFF(second, ClockIn, ClockOut)),
0)) AS VARCHAR(2)) + '.'
+ CAST(DATEPART(MI,
DATEADD(second,
SUM(DATEDIFF(second, ClockIn, ClockOut)), 0))
* 100 / 60 AS VARCHAR(2)) AS YourResult

--------------------------
http://connectsql.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-03 : 06:18:39

SELECT SUM(DATEDIFF(SECOND, ClockIn, ClockOut)) / 3600E

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 06:18:45

why not just simply


sum(datediff(second, ClockIn, ClockOut)) / 3600.0

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 06:19:24

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

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-03 : 06:33:20

@ lionofdezert:
I tried that, but am getting a result of "56.93", which is incorrect.

@ khtan:
the idea is to get the difference in time between all the clockIn's and outs for an employee, hence the complex statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 07:12:31

quote:
Originally posted by Jas

@ lionofdezert:
I tried that, but am getting a result of "56.93", which is incorrect.

@ khtan:
the idea is to get the difference in time between all the clockIn's and outs for an employee, hence the complex statement.

You don't need all the conversion if the end result you required is in decimal

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources