[AccessD] Access + SQL Server Date/Time Conclusions

Wed Jul 6 02:57:34 CDT 2022

Hi Paul

I didn’t write 3.333 ms, I wrote about 3.333 ms as the exact value varies and isn’t that important; actually, it is the average.

DateTime is preferred among most Access developers, because it will cause zero troubles and requires no concerns. It even works with the native ”SQL Server” ODBC driver of Windows, should you be forced to work with that in some locked down corporate environment.
Also, if you don’t operate with early dates and millisecond precision, DateTime2 offers nothing extra.

/gustav

Fra: Paul Wolstenholme<mailto:Paul.W at industrialcontrol.co.nz>
Sendt: 5. juli 2022 23:09
Til: Access Developers discussion and problem solving<mailto:accessd at databaseadvisors.com>
Emne: Re: [AccessD] Access + SQL Server Date/Time Conclusions

Gustav,

As you state, datetime has drawbacks over datetime2(3).

I agree that all but one version of datetime2 are not preferred in
conjunction with Access - according to my proof.
What is the basis for your implicit inclusion of datetime2(3) in your
assertion that "In relation to Access, DateTime2 is in no way preferred"?
Not preferred by whom and why?

As a side note, I would assert that the resolution of datetime is up to 4
ms, as per this example SQL Server code:
DECLARE @RawMin datetime2(7) = '2022-07-04 12:34:56.0045000'
DECLARE @RawMax datetime2(7) = '2022-07-04 12:34:56.0084999'

SELECT
    @RawMin AS RawMin
    , CAST(@RawMin AS datetime) AS RoundedFromMin
    , @RawMax AS RawMax
    , CAST(@RawMax AS datetime) AS RoundedFromMax
which yields:
RawMin                      RoundedFromMin          RawMax
     RoundedFromMax
--------------------------- -----------------------
--------------------------- -----------------------
2022-07-04 12:34:56.0045000 2022-07-04 12:34:56.007 2022-07-04
12:34:56.0084999 2022-07-04 12:34:56.007

The millisecond digit of datetime values is always resolved to a member of
{0, 3, 7}.  The example values above (differing by almost 4 ms) are
resolved to the same datetime value.

Paul Wolstenholme

On Tue, 5 Jul 2022 at 18:52, Gustav Brock via AccessD <
accessd at databaseadvisors.com> wrote:

> 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