[AccessD] Access 365 and Azure - DateTime fields

[David Emerson]

Hi Paul,

I had a table of over 36k records.  I ran some code to update them all and
didn't seem to have any problems.

As a check I will be looking at the tables this week to see that the new
records are all as expected.

Regards, David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Monday, 6 July 2020 2:49 p.m.
To: Access Developers discussion and problem solving
Cc: Paul Hartland
Subject: Re: [AccessD] Access 365 and Azure - DateTime fields

David,

Not sure how you tested (so I guess I am asking what you did). I think if I
were trying to test this, I would create a routine that ran the same query
using that SQL for a few minutes to see if I got any illogical entries. Did
you do something similar?

On Fri, Jun 26, 2020 at 5:20 PM David Emerson <newsgrps at dalyn.co.nz> wrote:

> The code is mostly part of a multiple insert line so using a variable to
> store the date won't work in this instance.
>
> I did a bit of testing and the clock flipping didn't seem to be an issue.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Paul Hartland via AccessD
> Sent: Friday, 26 June 2020 4:46 p.m.
> To: Access Developers discussion and problem solving
> Cc: Paul Hartland
> Subject: Re: [AccessD] Access 365 and Azure - DateTime fields
>
> I may of gone a slightly different route, instead of :
> DATEADD(ms, -DATEPART(ms, GETDATE()), GETDATE())
> I would of probably done this
> declare @dtMyDate as datetime
> set @dtmydate = getdate()
> set @mydate = dateadd(ms,-(dateadd(ms,-(), @mydate) ), @mydate)
>
> Paul
>
> On Thu, 25 Jun 2020, 21:26 David Emerson, <newsgrps at dalyn.co.nz> wrote:
>
> > I solved this one by removing the milliseconds this way:
> >
> > DATEADD(ms, -DATEPART(ms, GETDATE()), GETDATE())
> >
> > Thanks for the ideas.
> >
> > David
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > David Emerson
> > Sent: Tuesday, 23 June 2020 7:52 p.m.
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Access 365 and Azure - DateTime fields
> >
> > Previously I have had problems with Datetime2 not working with Access.
> > Have
> > things changed?
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Stuart McLachlan
> > Sent: Tuesday, 23 June 2020 7:34 p.m.
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Access 365 and Azure - DateTime fields
> >
> > Correction!  Datetime2 is 100 nanosecond precision, not icrosecond!
> >
> > On 23 Jun 2020 at 17:30, Stuart McLachlan wrote:
> >
> > > Try changing the  field to datetime2.
> > >
> > > https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transac
> > > t-sql?view=sql-server-v er15 <quote> DateTime.... 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. </quote>
> > >
> > > Sounds like the driver is trying to write a DateTime2 (microsecoonds)
> > > compatible value to a parameter bound to a  DateTime (milliseconds)
> > > field.
> > >
> > >
> > > On 23 Jun 2020 at 16:13, David Emerson wrote:
> > >
> > > > Hi Listers,
> > > >
> > > >
> > > >
> > > > Another problem with the conversion from SQL 2008 to Azure L
> > > >
> > > >
> > > >
> > > > When saving a record with a date field in it I am getting an error
> > > > "[Microsoft][ODBC Driver 17 for SQL Server]Datetime field overflow.
> > > > Fractional second precision exceeds the scale specified in the
> > > > parameter binding."
> > > >
> > > >
> > > >
> > > > What confuses me is that the record already has the date fields
> > > > filled in and saved.  All I am changing is a text field.
> > > >
> > > >
> > > >
> > > > I am using ADO to get the recordset for the form.  The SQL table
> > > > date fields are datetime SQL data type to match the Access date data
> > > > type.
> > > >
> > > >
> > > >
> > > > This article came the closest to having useful information but I am
> > > > not sure how to set the number of decimal digits.
> > > >
> > > >
> > > >
> > > > http://rightondevelopment.blogspot.com/2009/10/sql-server-native-cli
> > > > en t-100- datetime.html
> > > >
> > > >
> > > >
> > > > Can anyone steer me in the right direction?
> > > >
> > > >
> > > >
> > > > Regards
> > > >
> > > > David Emerson
> > > > Dalyn Software Ltd
> > > > Wellington, New Zealand