[AccessD] Access 365 and Azure - DateTime fields

Stuart McLachlan stuart at lexacorp.com.pg
Thu Jun 25 22:23:54 CDT 2020


Sure is.  :(

On 25 Jun 2020 at 22:25, Bill Benson wrote:

> Is there a possibility of the system clock flipping to a new value
> exactly in between the point when it calculates one of the two
> GETDATE() functions, and the other?
> 
> On Thu, Jun 25, 2020 at 4:26 PM 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-tra
> > > nsac 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
> 




More information about the AccessD mailing list