Working with only Time in Sql?
-
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.
-
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.
If minute level precision is fine then save the number of minutes and use int as datatype.
Declare @dt1 datetime, @dt2 datetime Set @dt1 = GetDate() Set @dt2 = DateAdd(yyyy, 1, @dt1) select DATEDIFF(mi, @dt1, @dt2)Farhan Noor Qureshi if (this == this) thow this;