I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
This is what I know too, and unfortunately knowing this does not solve my problem. So the the "solution" that I found until now is to enter a date in the second column and hours in the second and third. Aftwards, using a trigger to add the day part of the second column to the two hours columns. If someone has a quicker/simpler solution ... yes please.
Calculation of the fourth column is ok, but is stored, for example:
24/02/2004 8:00h 10:00h --> result for the difference 01/01/1900 2:00h