Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz
Understanding problems when converting time data to SQL Server

If you have SQL in Access queries which use only time values in a DATETIME field you could be in for a nasty shock when you start using linked tables.

The root of this problem is the way in which Access and SQL Server use a different date to mark data which only contains a time component.

When you enter only time data in Access, it gets a date part stamped with the date 30th December 1899. However, the default date that SQL Server uses when inserting a TIME is 1st January 1900. So read on to see the implications of this.

Consider the following Access query selecting times using Access tables of data.




Looking at the sql we see the use of the default date hidden in the grid:-

SELECT tblTimeData.StartTime, tblTimeData.EndTime
FROM tblTimeData
WHERE (((tblTimeData.StartTime)>#12/30/1899 13:45:0#));

Now after you have upsized and linked to your table in SQL Server, your above Access query using the linked table will no longer work, read on to see why?

The Profiler Helps Explain Why

The SQL Server profiler allows the tracing of the SQL being emmitted by Access when communicating with SQL Server and is an invaluable tool.

Looking at the trace we see the following :-

SELECT [StartTime], [ATimeId] FROM [tblTimeData] WHERE ([StartTime] > {t '13:45:00'})

The above looks like Access is relying on SQL Server making some kind of assumption about the default date to use.

And because of the miss-match between Access and SQL Server no data is displayed in Access.

Work Around

One acceptable work around is to modify your SQL to use a CDATE function to force an explicit entry of the date. The following example will fix the above problem in Access

SELECT tblTimeData.StartTime, tblTimeData.EndTime
FROM tblTimeData
WHERE (((tblTimeData.StartTime)>CDate("30/12/1899 13:45:00")));

Again returning to the trace generated by the above we see the following results.


exec sp_executesql N'SELECT "dbo"."tblTimeData"."ATimeId" FROM "dbo"."tblTimeData"
WHERE ("StartTime" > @P1
) ',N'@P1 datetime',''1899-12-30 13:45:00:000''

And because this does not make an assumptions about default dates, it works.


One More Gotcha!

Be very careful about getting code in SQL Server to enter a TIME, consider the following.

INSERT INTO tblTimeData(StartTime,EndTime)
VALUES('01:01:00','05:01:02')

Execute this in the Management Studio, and you will see the following.

SELECT Customers.CompanyName,
IIf(IsNull([Region]),"No Region Specified",[Region]) AS TheRegion
FROM Customers

Execute this in the Management Studio, and you will see the following.




And now you see that the default date in SQL Server for time data is the 1st January 1900. This could have a rather nasty impact when mixed with existing Access generated TIME data.

Converting To .net

If converting to .NET and using SQL Server to insert time data when there is already existing Access produced time data, you may be best to convert all the existing Access default dates to SQL Server default dates.


MUST Behaviour

Must will correctly access times parameters in your SQL where Access has entered the date such as #12/30/1899 13:45:0# in your SQL and also convert the use of a TIME function to the following.

CONVERT([datetime], '30 December 1899 ' + CONVERT(VARCHAR,GETDATE(),14),14)

Download Code

The sample code can be downloaded from here Access 2003 Database, which has examples of these queries.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz


Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz