Forum Discussion

BartNSTCL's avatar
BartNSTCL
Copper Contributor
Nov 03, 2025

Temporal Table - Change the ValidFrom Time

Sql Server 2022 - We needed a new 'Bonus' value to be active from 11/01/2025 06:00:00. I created a one time SQL job to update the temporal table for the Bonus value (going from 2.175 to 2.00) to run at 11/01/25 06:00:00. In the temporal table, the valid from is (converted to the local time) 2025-11-02 06:00:00.5656752 -06:00. If I pass FOR SYSTEM_TIME FROM '2025-11-02 06:00:00' TO '2999-01-01 23:59:59.999', I will get the previous since the job was just over a 1/2 second late. I've read articles talking about turn off versioning, then updating the History and current tables, then turning versioning back on. Seems like thats not possible with a generate always ValidFrom. I tried to create a temporal table where Validfrom as GENERATED BY DEFAULT but that didn't work. Is there a way I can get my validFrom to exactly 11/01/2025 06:00:00? Is there a better way in the future to get things set to exactly a desired time?

2 Replies

  • BartNSTCL's avatar
    BartNSTCL
    Copper Contributor
    Nov 10, 2025

    Thanks for the update. I think what Temporal tables were in the beginning were what I was looking for, but I have a better understanding about its limitations and what it would be more practical for. I had Copilot create a set of tables and triggers that would 'emulate' what I was looking for with Temporal tables. With that settled, this inquiry can be closed.

  • SivertSolem's avatar
    SivertSolem
    Iron Contributor
    Nov 10, 2025

    From what I understand from your question, what you really want is to query the table and only get rows where ValidFrom an ValidTo is in the timeframe FROM '2025-11-02 06:00:00' TO '2999-01-01 23:59:59.999'.

    I do believe changing your select to instead use CONTAINED IN ('2025-11-02 06:00:00', '2999-01-01 23:59:59.999') would give you the result you're looking for.
    Query data in a system-versioned temporal table - SQL Server | Microsoft Learn

    The first two subclauses [FROM ... TO/BETWEEN ... AND] return row versions that overlap with a specified period (that is, those that started before given period and ended after it), while CONTAINED IN returns only those that existed within specified period boundaries.

    As the ValidTo and ValidFrom colums are determined by the system time at transaction start, I don't believe there's any way to guarantee that the query runs at the exact time you want. There's just too many other factors involved.

Resources