Working with only Time in Sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Working with only Time in Sql?

Author

Topic

majidbhutta
Starting Member

13 Posts

Posted - 2005-11-29 : 07:04:01

i 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}
DateTime PrimaryKey }----> Composite Primary key

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-29 : 07:34:25

quote:
HoursWorked ?--------> What shoulud be DataType?

If you only need to keep in terms of hours then use int.
If you need to keep in 2 decimal then use decimal(6,2)

quote:
DateTime PrimaryKey }----> Composite Primary key
CheckInTime DateTime
CheckOutTime DateTime

These fields should be datetime

You should keep CheckInTime & CheckOutTime in both date & time. SQL provide function to calc date difference in day, hour, minutes etc.
To calc hours worked, you can use the following

datediff(hour, CheckInTime, CheckOutTime)  or
datediff(minute, CheckInTime, CheckOutTime) / 60.0
to calc hour works in decimal

-----------------
[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources