SQL Server and Access Zero Date Difference

Problem: How to store TIME values?

Recently while we were upsizing an MS Access application to SQL Server, we noticed some
strange behaviour regarding the way dates were displayed. Some data entry screens in our
MS Access client required users to enter time values in the format: 8:15 am, with no corresponding
date value. Although the application displayed and treated these values as pure time values with no
date portion, Access was storing a default date (or zero date) along with the time value.

In MS Access, the zero date is represented as 12/30/1899, which you can verify by typing the
following command into the MS Access Debug window (Ctl-G to open Debug Window):

 
Print “Zero Date: ” & Format(0,”Short Date”)
 
Zero Date: 12/30/1899

Our application was working fine until we changed the way new records were added. As long as new
values were inserted from the MS Access client, everything was fine and the time values continued
to display properly. But, once we started using an SQL Stored Proc to insert records, the time values
in the MS Access client began to display both date and time, the date being displayed as ‘1/1/1900’.

Below is the SQL used to call the stored proc, as well as the procedure itself. The time value was
passed to the @TimeStart argument as a string parameter with the format of ‘8:00 am’.

The stored proc, ems_InsertBooking, looks like this:

 
CREATE PROCEDURE ems_InsertBooking
     
@BookID INT
   
, @RoomID INT
   
, @BookDate DATETIME
   

Opening the table in SQL Server Enterprise Manager revealed the same peculiarity.
Previously entered data in TimeStart and TimeEnd fields displayed only the TIME portion,
whereas all new records inserted with our Stored Proc displayed 1/1/1900 along with the
Time value.

In other words, our Stored Proc was inserting the SQL Server default date (or zero date).
The default date may be displayed by executing the following statement from Query Analyzer:

    SELECT CONVERT(VARCHAR(12),CONVERT(DATETIME,0),103) As “Zero Date”
    ==================================================================
    Zero Date
    ————
    01/01/1900

Solution: Force the correct Zero DATE Value

So, if we want Access to treat a DATETIME field value as if it were TIME only, we cannot let SQL
Server insert it’s default or zero date. We need to force the DATE portion of the record to the
ACCESS DEFAULT, namely 1899-12-30. So, in our Stored Proc, we included this variable.

    DECLARE @strZeroDate CHAR(11)
    SET @strZeroDate = ‘1899-12-30’

And then concatenated it to the TIME parameter as follows:

  
INSERT INTO tblBooking
    
( BookID
    
, RoomID
    
, BookDate
    
, TimeStart
    
, TimeEnd )

Rewriting our INSERT statement in this fashion allows the Access User Interface (and the SQL
Enterprise Manager Interface) to display a TIME value where, in actuality, a DATETIME is stored.

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
from 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

See All Articles by Columnist Danny Lesandrini