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

[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.

 

[Pyro]

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.

SQL Server can handle the conversion internally. For example, I am in Brisbane, Australia, and the below will convert the value to my local equivalent.

SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' 
AT TIME ZONE 'E. Australia Standard Time' AS LocalTime;

AT TIME ZONE returns a datetimeoffset, handling things like daylight savings internally.

It is worth also mentioning that while SYSUTCDATETIME() returns a datetime2(7) value (as mentioned above), SQL Server will implicitly convert the value to datetime (if inserting/updating a datetime field). This means you don't really need to think about it unless you want to be explicit. In which case:

CAST(SYSUTCDATETIME() AS DATETIME)

...will handle the explicit conversion.

My preference is to let SQL Server do the work when ever possible. The table trigger will keep the LastUpdated value up to date regardless of where the update comes from, and the OP can ditch the extra UPDATE statement altogether.