Datetime field overflow when inserting data from Access (1 Viewer)

[bodders24]

I have an Azure SQL Server database (12.0.2000.8), and an Access 365 front-end.

I am using VBA code to insert data into a table. The problem occurs on the LastUpdatedDate field which is defined as DateTime in the SQL Server table.

It fails on this line of the VBA code to update the table:

rst !LastUpdatedDate = Format(Now, "yyyy-mm-dd hh:mm:ss.000")

where the rst is an ADODB.Recordset.

It returns this error:

[Microsoft][ODBC Driver 17 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

I have tried all different date formats without success.

Does anybody have any thoughts on resolving this

TIA

Bodders

 

[Gasman]

You should not store formatted dates.
Just use Now().

 

[bodders24]

You should not store formatted dates.
Just use Now().

I tried that already & it comes back with exactly the same error

 

[Minty]

That format won't work, and is a bit pointless as the now() function only returns whole seconds
Try format(Now, "yyyy-mm-dd hh:nn:ss")

If you were inserting it directly you would need to use single quotes around the value as that's what SQL Server expects 'yyyy-mm-dd hh:nn:ss'

 

[bodders24]

That format won't work, and is a bit pointless as the now() function only returns whole seconds
Try format(Now, "yyyy-mm-dd hh:nn:ss")

If you were inserting it directly you would need to use single quotes around the value as that's what SQL Server expects 'yyyy-mm-dd hh:nn:ss'

I just tried Format(Now, "yyyy-mm-dd hh:nn:ss") and still comes back with the Datetime field overflow.

Running this directly in SSMS works fine:

UPDATE dbo.tblNarrowFileObjects
SET LastUpdatedDate='2026-04-09 15:07:19'
WHERE NFObjectID =119

 

[bodders24]

After more trial and error this line actually works - not sure why that format does & not the others:

!LastUpdatedDate = Format(Now, "dd-mm-yyyy hh:nn:ss")

Thanks for the responses

Bodders

 

[The_Doc_Man]

The IEEE 754 64-bit format (DOUBLE) when typecast as DATE can hold time fractions to < 1 msec, but the formatting routines cannot translate it either way. To store time more accurately than to the second, you have to use a home-grown formatter.

Convert the time string to stop at seconds, then express your fractional seconds separately (with the leading decimal point). Divide that fraction by 86400 (seconds per day) and add that fraction to the date field. The reverse conversion is equally a pain in the nether regions.

Fortunately, as I verified by experiment for debugging event timing issues, you CAN use CDate(DOUBLE) and CDBL(Date) to interconvert the two formats without loss of precision. However, if at any time you pass in a Date-formatted variable to something that actually does some kind of time computations, you are pretty much done with the fractions. And the FORMAT function is the worst culprit of that behavior.

I looked this up because I'm not that familiar with SQL Server, but check out format datetime2 - which allows extended precision beyond seconds.
Use search string "SQL Server high precision time" which might give you some other alternatives.

 

[Minty]

Do NOT any under circumstances use DateTime2 as a data type in SQL Server unless you are forced to.

Access support for it is extremely flaky, and prone to strange errors, even if you tick the box that says supported:

This just doesn't work properly.

 

[bodders24]

After more trial and error this line actually works - not sure why that format does & not the others:

!LastUpdatedDate = Format(Now, "dd-mm-yyyy hh:nn:ss")

Thanks for the responses

Bodders

I got ahead of myself. This worked for one time only & then reverted back to the Datetime field overflow. Bizarre

 

[Minty]

Actually if this is being run in Access you need to create the date with # separators so

!LastUpdatedDate = Format(Now, "\#yyyy-mm-dd hh:nn:ss\#")

Will probably work.

Edit: Just as a check - if you look at the linked table in Access in design view what datatype does it think it is?

 

[The_Doc_Man]

Do NOT any under circumstances use DateTime2 as a data type in SQL Server unless you are forced to.

As I said, I had to look it up since I'm not intimately familiar with SQL server. The notes I saw didn't suggest there was an issue. I therefore have to fall back on the idea that a conversion via Access VBA between DOUBLE and DATE data types is a possible way to manipulate fractions of a second.

 

[cheekybuddha]

If you are using a recordset and updating a field, just use a Date.

rst!LastUpdatedDate = Now
' or
rst!LastUpdatedDate = #04/09/2026 17:56:19#

If you are using SQL then pass the date as yyyy-mm-dd hh:nn:ss

Dim strSQL As String

strSQL = "UPDATE YourTable SET DateField = " & Format(Now, "\'yyyy\-mm\-dd hh:nn:ss\'")
CurrentProject.Connection.Execute strSQL, adCmdText + adExecuteNoRecords

 

[isladogs]

If you really need higher precision than whole seconds, you can use the date time extended datatype in Access itself.
That can display to 7 d.p. (0.1 microseconds) but, as already mentioned, it does have limitations.

 

[Minty]

As I said, I had to look it up since I'm not intimately familiar with SQL server. The notes I saw didn't suggest there was an issue. I therefore have to fall back on the idea that a conversion via Access VBA between DOUBLE and DATE data types is a possible way to manipulate fractions of a second.

The main issue is a weird one - If you use GetDate() as a default value in a DateTime2 field in Azure SQL server the saved value has too many fractions of a second stored for Access to recognise and causes the record to become un-editable in Access.

It's a problem that has caught me out 3 or 4 times, with strange errors.

 

[GPGeorge]

I agree with Minty. This probably is related to the use of DateTime(2) in the SQL Server table (which I think is probably now the default). It doesn't play well with Access . If the precision is needed in your application, you'll need enable DateTimeExtended, but it still may not be totally consistent. Best to avoid it if you can.

 

[Isaac]

I just tried Format(Now, "yyyy-mm-dd hh:nn:ss") and still comes back with the Datetime field overflow.

Running this directly in SSMS works fine:

UPDATE dbo.tblNarrowFileObjects
SET LastUpdatedDate='2026-04-09 15:07:19'
WHERE NFObjectID =119

that's because nano isn't part of datetime

 

[Minty]

that's because nano isn't part of datetime

? The n in the format is used for minutes to avoid ambiguity with the m for month values.
Date Formatting

Most people leave it at hh:mm as VBA will assume you mean minutes, but it's not correct.

 

[bodders24]

Update from this morning. I tried all combinations of embedding the date within single quotes or hashes, but none of them worked.

In the end I generated a separate bit of SQL and ran it separately.

UPDATE tblNarrowFileObjects SET LastUpdatedDate = '2026-04-10 09:30:50' WHERE NFObjectID = 118

It's not ideal, but at least it works now.

Thanks for all your suggestions and comments

Bodders

 

[arnelgp]

using the code on your post #1:

rst!LastUpdatedDate = Now()

 

[Isaac]

? The n in the format is used for minutes to avoid ambiguity with the m for month values.
Date Formatting

Most people leave it at hh:mm as VBA will assume you mean minutes, but it's not correct.

My bad , ignore what I said. I was thinking the OP was trying to force a precision that just wasn't available in DateTime, but I can see that's not exactly the case actually