[AccessD] Access 365 and Azure - DateTime fields

[Bill Benson]

Poor Paul, he has been getting duplicate posts since June 26. Not sure why
we are using Reply-All as a reflex nowadays  :)

On Sun, Jul 5, 2020 at 10:48 PM Bill Benson <bensonforums at gmail.com> wrote:

> 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
>> >
>> > --
>> > AccessD mailing list
>> > AccessD at databaseadvisors.com
>> > http://databaseadvisors.com/mailman/listinfo/accessd
>> > Website: http://www.databaseadvisors.com
>> >
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>