time difference in hh.mm - 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

time difference in hh.mm

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-11 : 00:54:46

I have one computed column Shorthours.
In this computed column i want to display the result in hh.mm

empid FirstIn FirstOut EMPSIN EMPSOut hrworked shorthr

100 08:35:00 18:01:00 08:00:00 18:00:00 9.26 0.74

firstin,firstout is time in and timeout for emp
EMPSIN,EMPSOUt is expected timein and timeout for emp

firstin,firstout,empsin,empsout are in 00:00:00 format so to get the shorthr in time format hh.mm
i am getting the answer 0.74 (answer should be 1.14) using the formula below :

help needed

(case when (0)>((CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0)))) then (0) else (CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0))) end)

Sachin.Nand

2937 Posts

Posted - 2010-12-11 : 05:39:08

Why do you keep asking samething over and over again?Wasn't this replied here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153956

Almost the samething here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153776

and here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153792

PBUH

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-11 : 06:15:33

dear its little bit different

sorry if you are annoyed

Here i am using computed column where i am using four column to get the result.

firstout-firstin i am getting in hh.mm
EmpSOut-EmpSIN i am getting in hh.mm

but when i am trying to substract (EmpSOut-EmpSIN)-(FirstOut-FirstIn)

again i am getting answer in decimal form in my computed coloum.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-11 : 07:48:36

SELECT REPLACE(CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, EmpSin, EmpSout), 0), 108), ':', '.')

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

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-11 : 08:13:49

your explanation is correct but still its not solving the purpose

When i substract these two value i should get answer in hh.mm

SELECT REPLACE(CONVERT(float, DATEADD(SECOND, DATEDIFF(SECOND, EmpSIN, EmpSOut), 0), 108), ':', '.')
from AttendanceResult where EmpID='20'

-

SELECT REPLACE(CONVERT(float, DATEADD(SECOND, DATEDIFF(SECOND, FirstIn, FirstOut), 0), 108), ':', '.')
from AttendanceResult where EmpID='20'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 10:00:26

you mean this?


SELECT REPLACE(CONVERT(float, DATEADD(SECOND, DATEDIFF(SECOND, EmpSIN, EmpSOut)-DATEDIFF(SECOND, FirstIn, FirstOut), 0), 108), ':', '.') 
from AttendanceResult where EmpID='20'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-11 : 10:51:20

quote:
Originally posted by pnasz

your explanation is correct but still its not solving the purpose

Yes it is.

SELECT CAST(REPLACE(CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, '12:15:23', '13:11:02'), 0), 108), ':', '.') AS FLOAT)

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

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-11 : 11:30:11

thanx a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources