Sql-server – get SQL Server Transaction Begin Time from

sql servert-sqltemporal-tablestransaction

I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2

Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?

I have some non-system versioned tables that are part of the same transaction and I would prefer if the datetime2 recorded against them matched instead of using SysUTCDateTime and having it vary.

I tried to pull it from sys.dm_tran_active_transactions but its datetime and local server time and also appears to be different ?

system versioned: 2017-04-11 14:00:59.4690673

active transations: 2017-04-11 15:00:59.467

Best Answer

You can get the id of the current transaction from sys.dm_tran_current_transaction.

Then you can get the transaction_begin_time column from sys.dm_tran_active_transactions.

So, use one of the following T-SQL statements:

SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_current_transaction dtct
    INNER JOIN sys.dm_tran_active_transactions dtat 
        ON dtct.transaction_id = dtat.transaction_id;

or

SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_active_transactions dtat 
WHERE dtat.transaction_id = CURRENT_TRANSACTION_ID();

CURRENT_TRANSACTION_ID() returns the value of the transaction_id column from the sys.dm_tran_current_transaction.

Related Question