> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Linking to SQL Server Date Fields from Microsoft Access
- URL: https://nolongerset.com/sql-server-date-fields-ms-access/
- Published: 2021-09-19T00:32:41.000Z
- Updated: 2026-05-08T13:02:20.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: SQL Server, #Import 2026-05-20 02:59

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](https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-and-time-types?view=sql-server-ver15):

```sql
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](https://nolongerset.com/accessui-data-source-manager/) to link the table.*)

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-56.png)

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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-58.png)

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

- `[DATETIME](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15)`
- `[SMALLDATETIME](https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15)`

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](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15). Let's try relinking the test table using that driver instead:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-59.png)

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

- `[DATE](https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver15)`
- `[DATETIME2](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15)`

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-62.png)

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 ManagerTired of dealing with the limitations and poor UI of Access’s built-in linked table manager? Try this FREE improved version from Kevin Bell.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/05/Data-Source-Manager.jpg)](https://nolongerset.com/accessui-data-source-manager/)

### External references

[Date and time types - SQL ServerDate and time typesMicrosoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-and-time-types?view=sql-server-ver15)

[datetime (Transact-SQL) - SQL Serverdatetime (Transact-SQL)Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15)

[smalldatetime (Transact-SQL) - SQL Serversmalldatetime (Transact-SQL)Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15)

[Download ODBC Driver for SQL Server - ODBC Driver for SQL ServerDownload the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.Microsoft DocsDavid-Engel![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15)

[date (Transact-SQL) - SQL Serverdate (Transact-SQL)Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver15)

[datetime2 (Transact-SQL) - SQL Serverdatetime2 (Transact-SQL)Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15)

*Image by [Orna Wachman](https://pixabay.com/users/ornaw-8155178/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4058766) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4058766)*