Transaction's Date and Time - by Jason Kresowaty

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 365434 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 8/31/2008 10:41:41 AM
Access Restriction Public

Description

Provide DATETIME-returning functions that return a date and time that is the same throughout the transaction.  In the case that the transaction is under the SNAPSHOT isolation level, the DATETIME should equal the date and time when the snapshot was "taken", which must also be greater than or equal to the date and time when BEGIN TRANSACTION was executed.  For other isolation levels, handle this in whatever way makes sense.

For example:
GETTRANSACTIONDATE()
GETTRANSACTIONUTCDATE()

AFAIK, there is currently no way to get the date and time of the current snapshot.

--- Added the following on 9/5/2008 and reopened:

The workaround suggested by Microsoft does not work well because:

(1) It requires the server permission "VIEW SERVER STATE", which is too severe.  Moreover, this cannot be easily worked around due to the fact that this is a server-level permission. (EXECUTE AS could be used, but since it would require a LOGIN not a USER, this is especially inconvenient.)

(2) It does not return a UTC date. It is not possible to convert it to a UTC date and time that would be guaranteed to be correct; when the transaction occurs during the DST repeated hour it is impossible to tell whether the transaction started during standard or daylight time because the returned value is identical in both hours. UTC time is important to applications that have standardized on storing all dates and times in UTC.

Note that it is actually a common misconception that GETDATE()/GETUTCDATE() return the transaction time and date -- especially users coming from other database systems that use snapshots. Functions for returning this information can help these users and help in porting applications from other database systems.
Sign in to post a comment.
Posted by Jan [MSFT] on 3/29/2011 at 9:22 AM
Hello,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Jan
SQL Server Engine
Posted by Jason Kresowaty on 9/5/2008 at 5:33 PM
The workaround suggested by Microsoft does not work well because:

(1) It requires the server permission "VIEW SERVER STATE", which is too severe. Moreover, this cannot be easily worked around due to the fact that this is a server-level permission. (EXECUTE AS could be used, but since it would require a LOGIN not a USER, this is especially inconvenient.)

(2) It does not return a UTC date. It is not possible to convert it to a UTC date and time that would be guaranteed to be correct; when the transaction occurs during the DST repeated hour it is impossible to tell whether the transaction started during standard or daylight time because the returned value is identical in both hours. UTC time is important to applications that have standardized on storing all dates and times in UTC.

Note that it is actually a common misconception that GETDATE()/GETUTCDATE() return the transaction time and date -- especially users coming from other database systems that use snapshots. Functions for returning this information can help these users and help in porting applications from other database systems.
Posted by Umachandar [MSFT] on 9/2/2008 at 6:35 PM
Hi,
I have now resolved and closed the issue as explained.

Thanks
Umachandar, SQL Programmability Team

Posted by Umachandar [MSFT] on 9/2/2008 at 5:11 PM
Hi Jason,
Thanks for your suggestion. It is trivial to obtain the transaction begin time using the new DMVs in SQL Server 2005. See an example below:

begin transaction TestTran;
select t.transaction_begin_time
from sys.dm_tran_current_transaction as ct
join sys.dm_tran_active_transactions as t
    on ct.transaction_id = t.transaction_id;
rollback;

You can join with sys.dm_tran_active_snapshot_database_transactions to get other snapshot related information also.
So I will resolve this suggestion item as won't fix since the work of introducing built-ins for same functionality doesn't provide any additional value.

--
Umachandar, SQL Programmability Team