[AccessD] Access 365 and Azure - DateTime fields

[Paul Hartland]

To clear the millisecond just take it away from the date i.e.

update yourtable
Set yourdatefield = dateadd(millisecond, -(datepart(millisecond,
yourdatefield)), yourdatefield)

Paul

On Tue, 23 Jun 2020, 06:57 David Emerson, <newsgrps at dalyn.co.nz> wrote:

> Further information - I notice that some records can be changed and some
> can't.  The difference seems to be in the decimals stored in the field.
> For
> example, a record with the first date in a field will not save, but zeroing
> the decimals will enable it to be saved (see second date):
> 2020-06-23 17:21:32.312
> 2020-06-23 17:21:32.000
>
> This is true whether the field changed is a date field or any other field
> type.  It seems that the presence of just one field with non-zero decimal
> places in a date field will cause the problem.
>
> It looks like I need to clear out the fractions of seconds from my datetime
> fields.  Does anyone have a simple SQL Query I could use to do this?
>
> David
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> David Emerson
> Sent: Tuesday, 23 June 2020 4:13 p.m.
> To: AccessD
> Subject: [AccessD] Access 365 and Azure - DateTime fields
>
> 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-client-100-
> datetime.html
> <http://rightondevelopment.blogspot.com/2009/10/sql-server-native-client-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
>