time diff hh.mm

Question

Post reply

time diff hh.mm

pnasz

SSC Rookie

Points: 44
More actions
December 11, 2010 at 3:37 am

#104017

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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1033289-391-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bharat Panthee Default port Points: 1448 More actions · Answer 2

if shorttime in hh:mm format then just used:

select CAST((cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))/60 as varchar(10))+ ':' + cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))% 60 as varchar(10))) AS TIME),

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Bharat Panthee (12/11/2010)
if shorttime in hh:mm format then just used:
select CAST((cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))/60 as varchar(10))+ ':' + cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))% 60 as varchar(10))) AS TIME),

Heh... I guess "No replies to this thread please" doesn't mean anything to some folks. 😉

Take a look at Gail's post where she identifies the "other" post. The reason why us old-timers do this is to keep the answers and discussions for a given problem on a single post. That way, it's easier for others trying to find a similar answer, it keeps the discussion all in one place, and it keeps people from answering a question that's already been answered. It would be much appreciated if you'd honor such requests to post on the other thread in the future. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)