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