SQL Server

Linking to SQL Server Date Fields from Microsoft Access

I always avoided SQL Server's DATE type in favor of the DATETIME type's better compatibility with MS Access. A newer ODBC driver has me changing my mind.

Mike Wolfe

Sep 18, 2021 • 3 min read

I need to reconsider my opinion on the DATE data type in SQL Server.

For years, I avoided the DATE data type in favor of the DATETIME data type. I did this even in situations where I did not want the user to be able to store a time portion. Why? Because in a linked table in Access, the DATE data type shows up as a 10-character text field.

Date and Time Types in SQL Server

Let's create a table in SQL Server using all of the available date and time types:

CREATE TABLE dbo.DateTest (
  DateTime_Type datetime null
 ,Date_Type date null
 ,Time_Type time null
 ,DateTime2_Type datetime2 null
 ,DateTimeOffset_Type datetimeoffset null
 ,SmallDateTime_Type smalldatetime null
)

The Legacy SQL Server ODBC Driver

Now, let's link to that table using the SQL Server driver that has been built in to Windows for years. (NOTE: I used Kevin Bell's excellent Access UI data source manager tool to link the table.)

I wrote a ListFields function to assist with writing queries in code. Let's use that now to see how the fields from our test table appear when linked via the standard "SQL Server" driver:

As you can see, the only SQL Server date/time types that link as Jet Date types are:

DATETIME
SMALLDATETIME

If you look at the help pages for these two functions (linked above), you will find the following notice prominently displayed:

NOTE
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

The obvious problem is that those other types do not align with the Jet Date type...or do they?