SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Parallel Data Warehouse (PDW) Tip: Usage of SYSDATETIME() vs GETDATE()

Usage of GETDATE() dates back to the earliest versions of SQL server and has become the primary blade in our SQL swiss army knife when we want to generate the current system date and time. Currently, there is something missing in the current release of Microsoft’s Parallel Data Warehouse (PDW) appliance architecture…can you guess what it is?  You got it,  the beloved  GETDATE() function is nowhere to be found and we are stuck using the tiny blade to get the job done.

There is plenty that can be done with a simple GETDATE() call that we take for granted.  In PDW,  SYSDATETIME() is the only alternative to GETDATE() and is quite different all around, see below:

Beyone generating system date and time, SYSDATETIME() and GETDATE() are nondeterministic functions due to the fact that they generate different results each time they are executed.

-GETDATE() returns the current system timestamp to the milliseconds (per the datetime datatype) and
SYSDATETIME() returns the current system timestamp to the nanoseconds (per the datetime2 datatype)

-Unlike GETDATE(), SYSDATETIME() cannot be called with a simple SELECT GETDATE(). Instead, the SYSDATETIME() must be used in a fully qualified select statement against a table that has at least one row in it at all times (I choose to execute against dwsys.sys.all_colums)



--display current datetime
SELECT TOP(1) SYSDATETIME() as currentdate from dwsys.sys.all_columns

--get current datetime into a variable
declare @currentdate datetime
SET @currentdate = (SELECT Top(1) SYSDATETIME() as currentdate from dwsys.sys.all_columns)

--get current datetime in a view definition
SELECT DISTINCT SYSDATETIME() as currentdate from dwsys.sys.all_columns --see below for more info

To expand on usage described for views, you cannot use SYSDATETIME() in a view like you can with GETDATE(), which results in some nasty looking PDW adapted  t-sql (I hope someone can show us a better way to use SYSDATETIME() in view scripts on PDW!).

SMP: CREATE VIEW testView AS SELECT colA, getdate() from tablebla

PDW: CREATE VIEW AS SELECT colA, currentdate
          FROM dwsys.sys.all_columns
          CROSS JOIN (SELECT DISTINCT SYSDATETIME() as currentdate from dwsys.sys.all_columns) --notice the use of CROSS JOIN and DISTINCT!


Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence

I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.


Leave a comment on the original post [saldeloera.wordpress.com, opens in a new window]

Loading comments...