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:

Similarities

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.

Differences
-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)

i.e.

SMP: SELECT GETDATE() 
PDW:
--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!