Using Date Functions in TSQL
I was recently asked what was the scope of a date function in a TSQL script or procedure. I remembered it as having been resolved at the time the script or procedure was compiled and executed. Turns out my memory was flawed. I found out because I decided to test my memory.
First a ran a query returning all the different time functions as a single select statement. This query resulted in all results having the same time base, only different when changing time zone as built into the function.
SELECT 'SYSDATETIME()' as fn, SYSDATETIME() AS DTM
UNION ALL
SELECT 'SYSDATETIMEOFFSET()' as fn, SYSDATETIMEOFFSET() AS DTM
UNION ALL
SELECT 'SYSUTCDATETIME()' as fn, SYSUTCDATETIME() AS DTM
UNION ALL
SELECT 'CURRENT_TIMESTAMP' as fn, CURRENT_TIMESTAMP AS DTM
UNION ALL
SELECT 'GetDate()' as fn, GetDate() AS DTM
UNION ALL
SELECT 'GetUTCDate()' as fn, GetUTCDate() AS DTM
Then I decided to find out what would happen if I ran that same query a second time preceeded by a five second delay, all running in a single execution.
SELECT 'SYSDATETIME()' as fn, SYSDATETIME() AS DTM
UNION ALL
SELECT 'SYSDATETIMEOFFSET()' as fn, SYSDATETIMEOFFSET() AS DTM
UNION ALL
SELECT 'SYSUTCDATETIME()' as fn, SYSUTCDATETIME() AS DTM
UNION ALL
SELECT 'CURRENT_TIMESTAMP' as fn, CURRENT_TIMESTAMP AS DTM
UNION ALL
SELECT 'GetDate()' as fn, GetDate() AS DTM
UNION ALL
SELECT 'GetUTCDate()' as fn, GetUTCDate() AS DTM
WAITFOR DELAY '00:00:05'
SELECT 'SYSDATETIME()' as fn, SYSDATETIME() AS DTM
UNION ALL
SELECT 'SYSDATETIMEOFFSET()' as fn, SYSDATETIMEOFFSET() AS DTM
UNION ALL
SELECT 'SYSUTCDATETIME()' as fn, SYSUTCDATETIME() AS DTM
UNION ALL
SELECT 'CURRENT_TIMESTAMP' as fn, CURRENT_TIMESTAMP AS DTM
UNION ALL
SELECT 'GetDate()' as fn, GetDate() AS DTM
UNION ALL
SELECT 'GetUTCDate()' as fn, GetUTCDate() AS DTM
In this case the dates from the second set were five seconds later than the first set. That was not how I remembered things happening. So, I put the whole thing inside a stored procedure, just like the second test. Running the stored procedure resulted in the same results with a five second gap, just as run inside an SQL script.
What happens if an SQL statement manipulating data takes a few seconds to execute, and it uses a calculated date function? That was my last test.
I created a table with two columns, an integer and a datetime. Then I used a sequence table with 10,000 rows and ran an insert statement taking six seconds to complete.
CREATE TABLE #Test (Number int, DTM DateTime)
INSERT INTO #Test
SELECT n1.Number * n2.Number, getdate()
FROM Numbers n1
CROSS JOIN Numbers n2
WHERE n1.number between 1 and 1000
AND n2.Number between 1 and 100
SELECT DISTINCT (dtm) from #Test
As I remembered, only one date value was generated. This is in keeping with the first query where each SQL statement maintains the same generated date, regardless of the time required to execute the statement. Only another statement, even executed in the same scope or transaction, will have a different calculated value for Date or Time.
This example should provide some guidance when utilizing date functions in scripts or procedures. If you wish to havethe same Date or Time for the entire execution period then use a variable and set the value of the variable at the beginning of the script. If using more than one stored procedure, use an input parameter for the timestamp.
If you wish to have different dates or times, then separate your SQL statements, and use a date/time function within each. In this manner, each statement will have a different result.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Seven Ways to Talk Yourself Out of Your Dream Job
Steve WynKoop and I talk a lot about designing and managing our professional careers on a weekly interview on SSWUG.org. This episode was about how we talk ourselves out of that dream job or interview. This article goes into more detail.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)