Working only With Time in Sql?

Question

Working only With Time in Sql?

Majid Bhutta

Old Hand

Points: 312
More actions
November 29, 2005 at 6:11 am

#88583

have some confusion. Scenario is that for an Employee to start and end job the following fields are available;
Field                  DataType
=====               ========
Emp_Id               Foreign Key}
pkdt                   DateTime PrimaryKey
CheckInTime        DateTime
CheckOutTime      DateTime
InOutStatus         bit
HoursWorked       ?---> What shoulud be DataType?
When an Employee Checks in All fields are set and InOutStatus is set to 1 Except CheckOutTime and HoursWorked.When The Employee checks out InOutStatus is set to 0,CheckOutTime is set and then i have to calculate the HoursWorked (TimeDuaration). Constarins are CheckOutTime is
always greater than CheckInTime.(There may be different dates for them as employee gets check in ,Nov 29,2005 on time 17:00 and checksout , Nov 30,2005 on time 03:30).Now according to calculation he worked for 10 hours and 30
minutes .How to calculate this hours worked(only time duration and to which datatype it should be saved in sql) as well as the Stored Procedure for this whole process.Whenever Employee comes his Emp_Id is only known.
Thnx in Advance.

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

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

Brian Munier SSCrazy Points: 2875 More actions · Answer 1

As you may have found out: DateDiff is useless for your problem as it only compares a part of the the date/time. Fortunately I was able to figure this out:

Use a datetime datatype for your hours worked, calcuate it by simple subtraction Time stopped - time started, and then CAST the resulting difference as a float and multiply it appropriately to convert from days to hours, to minutes or seconds.

Use the following code to test your question:

DECLARE @sd datetime, @ed datetime

SELECT @sd = '2005-11-29 17:00:00.917'

SELECT @ed = '2005-11-30 03:30:12.817'

SELECT @sd, @ed

SELECT CAST(@ed - @sd AS FLOAT), CAST(@ed - @sd AS FLOAT) * 24 AS HOURS

, CAST(@ed - @sd AS FLOAT) * 24 * 60 AS MINUTES

, CAST(@ed - @sd AS FLOAT) * 24 * 60 * 60 AS Seconds

Good luck,

Brian

bledu SSChampion Points: 12335 More actions · Answer 2

i would suggest

SELECT DATEDIFF(mi, CheckInTime, CheckOutTime) AS MinutesWorked

the datatype would be integer and then when you are displaying the hours worked in a report you convert to hours and minutes

Everything you can imagine is real.