If you had to pick one data type that causes the most problems during an Access migration, it would be dates. Not because dates are inherently complex — but because Access is exceptionally loose about how it handles them, and every server database is stricter.

Here is every way dates will trip you up and how to handle each one.

How Access Stores Dates

Internally, Access stores dates as double-precision floating-point numbers. The integer part represents the number of days since December 30, 1899. The decimal part represents the time as a fraction of a day.

This system is flexible. Access can represent dates from the year 100 to the year 9999. It accepts almost any date format you type. It stores and compares dates as simple numbers.

How Server Databases Store Dates

Each server database has its own date storage format, and each has limits that Access does not:

SQL Server: - DATETIME — January 1, 1753 to December 31, 9999. Precision: 3.33 milliseconds. - DATETIME2 — January 1, 0001 to December 31, 9999. Precision: 100 nanoseconds. - DATE — Date only, no time component. - TIME — Time only, no date component.

MySQL: - DATETIME — January 1, 1000 to December 31, 9999. Precision: microseconds. - DATE — Date only. - TIMESTAMP — January 1, 1970 to January 19, 2038. Stored as UTC.

PostgreSQL: - TIMESTAMP — 4713 BC to 294276 AD. Precision: microseconds. - DATE — Date only. - TIME — Time only.

Problem 1: Invalid Dates in Access Data

Access accepts values in date fields that are not real dates. Over years of use, Access databases accumulate invalid dates that server databases will reject.

Common invalid dates: - Empty strings ("") in date fields — Access treats these differently from NULL - 00/00/0000 or 0/0/0 — sometimes entered as placeholders - 12/31/9999 — used as a “no end date” sentinel - Dates before 1753 — valid in Access, invalid in SQL Server’s DATETIME

What happens during migration: The migration tool inserts these values into the server table. The server rejects them with a data type conversion error. The migration fails for those records — or worse, it silently substitutes NULL and you do not notice until someone runs a report.

Fix: Before migration, run these queries in Access to find problems:

SELECT * FROM YourTable WHERE DateField < #1/1/1753#
SELECT * FROM YourTable WHERE DateField IS NULL
SELECT * FROM YourTable WHERE IsDate(DateField) = False

Decide what each invalid date should become: NULL, a specific default date, or corrected to the intended value. Fix them before running the migration.

Problem 2: Date Delimiter Differences

Access uses hash marks (#) to delimit date values in SQL:

WHERE OrderDate > #6/15/2026#

Server databases use single quotes:

WHERE OrderDate > '2026-06-15'

What happens: Queries that use # delimiters work in Access but fail when sent to the server as pass-through queries. If Access sends the query through a linked table, it translates the delimiters automatically. But VBA code that builds SQL strings with # delimiters and executes them directly against the server will fail.

Fix: In any VBA code that builds SQL for server execution, use single quotes and ISO date format (YYYY-MM-DD):

' Access style (local only):
sSQL = "SELECT * FROM Orders WHERE OrderDate > #" & Me.StartDate & "#"

' Server style (pass-through or direct execution):
sSQL = "SELECT * FROM Orders WHERE OrderDate > '" & Format(Me.StartDate, "yyyy-mm-dd") & "'"

Problem 3: Date Format Ambiguity

Is 01/02/2026 January 2 or February 1? In Access, it depends on the user’s Windows regional settings. In the United States, it is January 2. In the United Kingdom, it is February 1.

Server databases also have regional settings that affect date interpretation. If your Access users are in the US but your SQL Server is configured for UK date format, every date with the day and month both under 13 will be misinterpreted.

Fix: Always use ISO 8601 format (YYYY-MM-DD) in SQL queries and data transfers. 2026-01-02 is unambiguous everywhere. Set this as the default format in your VBA date formatting.

Problem 4: Time Precision Differences

Access stores times with millisecond precision (as fractions of a day in floating-point). SQL Server’s DATETIME type rounds to the nearest 3.33 milliseconds, which means some time values change slightly during migration.

This rarely matters for business applications, but it can break exact-match queries:

-- This might find zero records after migration if the time was rounded:
WHERE CreatedAt = '2026-06-15 14:30:00.003'

Fix: Avoid exact-match queries on datetime fields. Use range queries instead:

WHERE CreatedAt >= '2026-06-15 14:30:00' AND CreatedAt < '2026-06-15 14:30:01'

Or use DATETIME2 in SQL Server, which preserves sub-millisecond precision.

Problem 5: Date Functions Differ

Access has its own set of date functions. Each server database has a different set:

Operation Access SQL Server MySQL PostgreSQL
Current date Date() GETDATE() CURDATE() CURRENT_DATE
Current date+time Now() GETDATE() NOW() NOW()
Add days DateAdd("d",7,date) DATEADD(day,7,date) DATE_ADD(date, INTERVAL 7 DAY) date + INTERVAL '7 days'
Difference DateDiff("d",date1,date2) DATEDIFF(day,date1,date2) DATEDIFF(date1,date2) date2 - date1
Extract year Year(date) YEAR(date) YEAR(date) EXTRACT(YEAR FROM date)
Format Format(date,"yyyy-mm") FORMAT(date,'yyyy-MM') DATE_FORMAT(date,'%Y-%m') TO_CHAR(date,'YYYY-MM')

What happens: Queries using Access date functions work through linked tables (Access translates them). But pass-through queries and VBA code that builds SQL strings must use the server’s syntax.

Fix: Learn the equivalent functions for your target database. For VBA code, build a helper function that generates the correct date SQL for your target:

Function SQLDate(dt As Date) As String
    SQLDate = "'" & Format(dt, "yyyy-mm-dd hh:nn:ss") & "'"
End Function

Problem 6: Time Zone Handling

Access has no concept of time zones. Dates are stored as raw numbers with no timezone information.

Server databases can be timezone-aware: - SQL Server’s DATETIMEOFFSET stores timezone offset - MySQL’s TIMESTAMP converts to UTC on storage - PostgreSQL’s TIMESTAMP WITH TIME ZONE stores in UTC

If your Access database stores times that are implicitly in a specific timezone, and the server is configured for a different timezone or UTC, time values can shift during migration.

Fix: Decide on a timezone strategy before migration. The simplest approach: use timezone-naive types on the server (DATETIME2 for SQL Server, DATETIME for MySQL, TIMESTAMP WITHOUT TIME ZONE for PostgreSQL) and treat all times the same way Access did — as local time with no timezone awareness.

The Pre-Migration Date Checklist

  1. Find and fix all invalid dates (nulls, empty strings, placeholder dates, pre-1753 dates)
  2. Choose the target date type (DATETIME2 for SQL Server is safest)
  3. Convert all VBA date SQL from # delimiters to ISO format with single quotes
  4. Replace Access date functions with server equivalents in pass-through queries
  5. Test date-boundary reports (first/last day of month, year boundaries)
  6. Test date calculations (age calculations, duration calculations, deadline calculations)
  7. Verify that date sorting produces the same order as Access

Dates touch almost every part of a business application. Fixing date issues before migration prevents the single largest category of post-migration bugs.