[AccessD] Access + SQL Server Date/Time Conclusions

[Gustav Brock]

Hi Paul

You could skip all these worries and just use DateTime in SQL Server. Compared to DateTime of Access, it has only two drawbacks which probably will be of no importance for most:

1. Earliest date of its range is 1753-01-01
2. Resolution is only about 3.333 ms

In relation to Access, DateTime2 is in no way preferred, on the contrary, neither are Date and Time which are subsets of DateTime2.
Also, the old date/time data type of SQL Server - SmallDateTime - should never be used. It is very limited and can be regarded as obsolete.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Paul Wolstenholme
Sendt: 5. juli 2022 07:55
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] Access + SQL Server Date/Time Conclusions

Hi,

I've had a hard look at using Access with an ODBC link to SQL Server back end tables to understand the issues that cause Date/Time problems.

My short answer is that I will now use the Access Date/Time data type and the SQL Server datetime2(3) data type only for date and time handling to avoid problems.

If you want the long answer...

The Access Date/Time extended data type was introduced in Access 2021.  It is NOT compatible with older versions of Access.  My customer is not ready for it - I can not use it.

Tests were done using Access 2010, OBBC Driver for SQL Server v17 (Release
number: 17.6.1.1, Released: July 31, 2020) and SQL Server Express 2014.
I did not try newer releases of the driver (v18 was released earlier this year but is not listed on connectionstrings.com) because the release notes don't refer to related issues.

*** In all tests performed, Access sent values to SQL Server as multiples of 1 millisecond (0.001 s). ***

Values entered into a table (or presumably a form) and programmed date constants are only accepted as multiples of 1 second.
In Access 2010 a variable of type double can be converted to a Date type (
CDate() or CV Date() ) yielding a resolution of under 1 microsecond (<
0.000001 s).  Access 2010 only shows these values rounded to a multiple of
1 second.
Access 2010 can retrieve values from Date/Time fields of Access tables with a resolution of under 10 microseconds (< 0.000010 s).  Access 2010 only shows these values rounded to a multiple of 1 second.
Values retrieved from elsewhere, such as linked SQL Server tables can also contain small time increments.  It would appear these are held in Access
2010 as Date types.

Problem 1:
When editing a SQL Server table in Access, Access will always check whether any value has changed before saving a record and warn that another user has made a change during editing.
A false warning can be produced as a result of data rounding in which case the user receives the warning and can not save the change.  It seems this only occurs if the following 2 conditions occur:
1) The SQL Server table has no column of type 'timestamp' (meaning row version).  This means Access can only check for changes by comparing data values in all columns with values retrieved earlier.
2) SQL Server has provided a non-zero value in a column after the 1 millisecond column of any field.
Solution 1:
Either avoid datetime2(n) where n>3 or always remember to add a timestamp column (or better still do both).

Problem 2:
Whenever Access tries to save a value to a SQL Server table that includes non-zero values in a column not supported by the SQL Server field, a data error is raised preventing the record from being stored.
This includes trying to save a Date that rounds to 2022-07-04 12:34:56.001 in a datetime2(2) field.  2022-07-04 12:34:56.010 can be saved successfully in the same field with no data error.
Solution 2:
Because Access 2010 is rounding values to 1 ms precision before sending it to SQL Server, avoiding the use of datetime2(n) where n<3 solves the problem in all data cases.

Microsoft has advised that datetime should no longer be used for new work in SQL Server.  This leaves datetime2(3) as the suitable replacement to minimise problems.

Note that Access 2021 does support the new Date/Time Extended data type and states it is compatible with SQL Server datetime2 (presumably datetime2(7).  In that case Access 2021 will be sending data to SQL Server data as multiples of 100 ns (0.0000001 s).
I am hopeful that no further issues have been introduced in that environment provided I avoid using Date/Time Extended and avoid linking Access to tables with datetime2(7).

My plan to avoid the repetition of the data error that bugged me is to replace all datetime2(n) fields, where n <> 3 with datetime2(3).
Circumstances might mean they don't all need changing immediately, but if I don't change them then future updates will make the system vulnerable to further data errors cropping up.

Paul Wolstenholme