When working with time values in SQL Server, it is important to understand how the database handles dates and times. In some cases, storing time values without a corresponding date can lead to unexpected behavior.
For example, let’s say we have an application that requires users to enter time values in the format of “8:15 am” with no date portion. In MS Access, the zero date is represented as 12/30/1899. However, when upsizing the application to SQL Server, we noticed that the time values were being stored with a default date of 1/1/1900.
This discrepancy became apparent when we started using an SQL Stored Procedure to insert records. The time values in the MS Access client began to display both the date and time, with the date being displayed as ‘1/1/1900’.
To resolve this issue, we need to force the correct zero date value in SQL Server. In our Stored Procedure, we can include a variable that represents the Access default date, which is 1899-12-30. We can then concatenate this variable with the time parameter when inserting records.
Here is an example of how the modified INSERT statement would look:
DECLARE @strZeroDate CHAR(11)
SET @strZeroDate = '1899-12-30'
INSERT INTO tblBooking (BookID, RoomID, BookDate, TimeStart, TimeEnd)
VALUES (@BookID, @RoomID, @BookDate, @strZeroDate + @TimeStart, @strZeroDate + @TimeEnd)
By rewriting our INSERT statement in this fashion, we can ensure that the Access User Interface (and the SQL Enterprise Manager Interface) will display a time value where, in actuality, a datetime is stored.
It is important to note that this solution is specific to SQL Server and may not be applicable to other database systems. Additionally, it is always recommended to thoroughly test any changes made to your database to ensure they do not have unintended consequences.
References:
- Microsoft Jet Database Engine Programmer’s Guide
- Appendix A Specifications, Data Types – Lee Woods, Microsoft Jet Product Unit Manager
- ACC: Query with Time Criteria Returns No Records – Microsoft SQL ServerID: Q173097
- Building Year 2000-Compliant Applications with Visual Studio 6.0 and Microsoft Windows DNA – Jerry Brunning, Clarity Consulting, Inc., August 1998
- XL: Access ODBC Driver Returns Date of 12/30/1899 – ID: Q125849