Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Sep 14, 2000

SQL Server and Access Zero Date Difference

By Danny Lesandrini

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'.

  EXEC ems_InsertBooking
   , @RoomID=639
   , @BookDate='9/8/2000'
   , @TimeStart='8:00:00 AM'
   , @TimeEnd='11:00:00 AM'

The stored proc, ems_InsertBooking, looks like this:

  CREATE PROCEDURE ems_InsertBooking
      @BookID INT
    , @RoomID INT
    , @BookDate DATETIME
    , @TimeStart VARCHAR(8)
    , @TimeEnd VARCHAR(8)


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

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:

    Zero Date

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 )

     ( @BookID
     , @RoomID
     , @BookDate
     , @strZeroDate + @TimeStart
     , @strZeroDate + @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.


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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM