June 14, 2005 at 7:44 pm
I am new with SQL and I am designing a database for myself which deals mainly with Time and Dates. I am having problem calculating time difference. I have the StartTime column and EndTime column and then HoursWorked column. My problem is how to get the Hours worked. Example I start work at 20:30 and closes at 07:30 the next morning which is 11 hours and some of the Times are the same day. Example 08:30 to 20:30 same day. Can anyone help me since I am new. thanks:
June 14, 2005 at 8:44 pm
You have to keep the whole date to easily solve this problem :
Select datediff(d, '2005/01/01 20:00.000', '2005/01/02 04:00.000') = 480 minutes (8 hours)
This is the easy way out. There's surely a way to calculate that number with only the time, but I don't have time to do it right now.
June 15, 2005 at 12:02 am
Like Remi says, you should really be storing the date as well. And both date and time should be stored in a single datetime column! (How are you storing time now by the way?)
And another question, you say you "have the StartTime column and EndTime column and then HoursWorked column". Does that mean you are storing these columns, or just want to output data in those columns to the client? If the former then your design is not good, you should not store the same data twice.
Anyway, just to answer your question, you could do something like this, depending on how your data is stored (here I assume it is always stored like '08:30').
DATEDIFF(hh
, CAST( CONVERT(VARCHAR(11), GETDATE(), 126) + starttime + ':00.000' AS DATETIME)
, CAST( CONVERT(VARCHAR(11), DATEADD(d, CASE WHEN starttime > endtime THEN 1 ELSE 0 END, GETDATE()), 126) + endtime + ':00.000' AS DATETIME)
)
However, note a couple of things about this: First, it only returns full hours. But it shows the principle you will need to use; add a date portion (any date will do) to the time portions, then add one to the date of the endtime if starttime is after endtime, and now use date manipulation functions to calculate the difference between the dates. Also note that this depends on you storing times with leading zero if needed, and always as five characters.
As you can see, this would be much easier if there was two full datetimes stored from the start.
June 15, 2005 at 8:11 pm
Hi Chris Hedgate, Thanks, I am storing in the StarTime, EndTime and the HoursWorked columns and I want to ourt put it as Well. Please advise me if it is not good design since I am studing on my own and not getting books which talks about datbase design. Thanks for your help I hope to hear from you
June 16, 2005 at 7:54 am
As I said, store the full date. Use smalldatetime if you only precision to the minute. Use datetime for precision to 3 ms. The hours worked column should not be stored in the db. It can easily be calculated when you query the database.
And go by a book... It's gonna save you a lot of hours of waiting on these boards.
June 16, 2005 at 8:33 am
Agree... store date/time together, but you can also "store" the hours work in a computed column if you'd like. Check out "computed columns" in BOL for details.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply