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.

Linking to SQL Server Date Fields from Microsoft Access

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:

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?

ODBC Driver 17 for SQL Server

As of writing, the latest available ODBC driver for SQL Server is ODBC Driver 17.  Let's try relinking the test table using that driver instead:

When we do that, the following additional SQL Server data types are also treated as Jet Date fields:

To be clear, I have never used the Date or DateTime2 SQL Server data types in a production Access application.  Your mileage may vary.

If you have used these two SQL Server date types, please let me know the pros and cons of your experience in the comments below.

Referenced articles

AccessUI - Data Source Manager
Tired of dealing with the limitations and poor UI of Access’s built-in linked table manager? Try this FREE improved version from Kevin Bell.

External references

Date and time types - SQL Server
Date and time types
datetime (Transact-SQL) - SQL Server
datetime (Transact-SQL)
smalldatetime (Transact-SQL) - SQL Server
smalldatetime (Transact-SQL)
Download ODBC Driver for SQL Server - ODBC Driver for SQL Server
Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.
date (Transact-SQL) - SQL Server
date (Transact-SQL)
datetime2 (Transact-SQL) - SQL Server
datetime2 (Transact-SQL)

Image by Orna Wachman from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0