Datetime field overflow when inserting data from Access

[Pyro]

Why not move this logic to the server? Create an AFTER UPDATE trigger directly on tblNarrowFileObjects. Something like:

CREATE TRIGGER trg_UpdateLastUpdatedDate
ON tblNarrowFileObjects AFTER UPDATE AS
BEGIN
    UPDATE tblNarrowFileObjects
    SET LastUpdatedDate = SYSUTCDATETIME()
    FROM tblNarrowFileObjects t
    INNER JOIN inserted i ON t.ID = i.ID;
END;

 

[The_Doc_Man]

Thank you, @Pyro, for bringing up the UTC functions.

SYSUTCDATETIME() vs GETUTCDATE() in SQL Server: What’s the Difference?

SYSUTCDATETIME (Transact-SQL) - SQL Server

SYSUTCDATETIME returns a datetime2 value that contains the current date and time of the system.

learn.microsoft.com

 

[sonic8]

SYSUTCDATETIME() vs GETUTCDATE() in SQL Server: What’s the Difference?

The first returns an DateTime2(7) the other a DateTime data type.

 

[The_Doc_Man]

The first returns an DateTime2(7) the other a DateTime data type.

I'm just pointing out that there ARE ways to get that extended fractional time. Didn't say it would be ideal. And that's also why I put up a couple of links - to let folks read and decide whether it was worth the effort.

UTC is at least a possibility. You just can't use Access formatting routines for UTC times. You would have to involve something that "knows" UTC formats. But it is not impossible to get that extra precision. And as noted, there IS a way to get fractional times for a DATE formatted number.