Is TemporalTable SysStartTime available before performing an insert/Update?

Question

Post reply

Is TemporalTable SysStartTime available before performing an insert/Update?

Dwaine Wright

SSC Veteran

Points: 212
More actions
March 31, 2023 at 6:48 pm

#4167702

I want to align a Date column (datetime2(7)) with the same full resolution date of SysStartTime column populated by SQL in temporal tables.
I know this is the "Transaction Start" datetime, and has full resolution (e.g., 2023-03-31 17:08:08.5344576)
I've attempted to use the following:
SELECT dt.transaction_begin_time AT TIME ZONE 'UTC'
FROM sys.dm_tran_active_transactions dt
WHERE dt.transaction_id = CURRENT_TRANSACTION_ID()
but the transaction_begin_time is only DateTime (e.g., 2023-03-31 14:38:06.6470000).
Is there another method to get at the full resolution date before performing an insert/update?
I don't like the idea of performing some DML to get the date and then going back and updating the column with the real SysStartDate. Just a bad idea for many reasons, not the least of which is creating another history row for no other reason.
The full datetime2 must be available somewhere since sql uses it when populating Temporal date fields!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Just to ask the question, have you checked the datatype for those columns in the base table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 2

To expand upon SSC Guru's question there are two types of Datetime data types and the second one is variable. The first data type being the now deprecated from Standard SQLs -- Datetime and the second one being Datetime2 which has percisions of 1 to 7 or Datetime2(3) for instance which by the way is the same percision as a Datetime data type but more accurate and uses 1 less byte. While Datetime2(0) uses 3 less bytes and the default of Datetime2 is Datetime(7) which uses the same number of bytes as a Datetime does.

That being said if the data type is Datetime then it is not only going to get truncated to 3 precision but it is also going to get rounded to one of three values ending with either .000, .003, or .007 which subsequently is what your example ends in 6.64(7) thus highly suggesting that it is getting stored in a deprecated Datetime data type.

Dwaine Wright SSC Veteran Points: 212 More actions · Answer 3

We use DateTime2(7) everywhere. MS also uses this in all of their sample scripts and the SSMS template for temporal tables...

A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END

From https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver16

So the question is, how can one determine the current transaction start date at datetime2(7) resolution?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Lordy. My apologies. I didn't look at the "record layout" for sys.dm_tran_active_transactions (from your query you posted)... The bloody database_transaction_begin_time column in that DMV is returned, by the system, as a DATETIME. [major face-palm][head-desk]

One thing that I DO know for sure is that anything >= .9983334 for fractional time will be rounded to the next second when converted to DATETIME or even TIME(3) (which also does rounding). There are similar issues with other "ranges".

For a different project, I actually am working on combining a DATE and TIME(7) column to a DATETIME column but I've not finished my testing on it yet and probably won't until this weekend. I can also tell you that, by itself, it won't be SARGable. You'd have to limit the range (which can SEEK) and then scan through that to get the match but that's a part of the testing that I've not yet completed.

There is an "endcap" for just the time element and only if you don't mix it with a date for the conversion to DATETIME (they screwed that up as of SQL Server 2016).

You can "fake" it by converting the TIME portion of a DATETIME2(7) to a TIME(3) (to take advantage of the "endcap" that prevents a rollover to 0 for anything >= .9983334 and, if the the MOD 10 of the "ms" datepart = 9, then subtract 2 ms from the TIME(3) prior to converting that to a DATETIME time and adding it back to the DATE by converting that 2 a DATETIME and simply adding the 2 together. Everything else should handle auto-magically)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 5

Okay it appears Jeff Moden figured out where the Datetime value was coming from.

Still perhaps this article will help some as you move forward with your project.

This reply was modified 1 year, 8 months ago by Dennis Jensen. Reason: Jeff Moden posted a reply just before mine that negated most of my post

Ken McKelvey SSCoach Points: 18859 More actions · Answer 6

Dwaine Wright wrote:

I want to align a Date column (datetime2(7)) with the same full resolution date of SysStartTime column populated by SQL in temporal tables.

Maybe I am misunderstanding something but why do you want two columns in the same table to have exactly the same datetime2 value? What is wrong with using the SysStartTime Column? If for some obscure reason you really want the same value in two columns why not create a computed column?