Search notes:

SQL Server: functions and expressions that evaluate to 'now'

The following expressions and T-SQL functions evaluate to a value which corresponds to «now» in SQL Server: 
select
   getDate             ()  getDate_,
   getUTCDate          ()  getUTCDate_,
   sysDateTime         ()  sysDateTime_,
   sysUTCDateTime      ()  sysUTCDateTime_,
   sysDateTimeOffset   ()  sysDateTimeOffset_,
   current_timestamp       current_timestamp_
getdate() returns a datetime datatype (whose precision is about 3 to 4 milliseconds).
sysdatetime() returns a datetime2(7) datatype (whose precision is in the nano seconds).
sysdatetime (or potentially sysutcdatetime) should be preferred over getdate().

Data types

With sql_variant_property(…, 'basetype'), it's possible to show the returend data type: 
select
   sql_variant_property( getDate             (), 'basetype')  getDate_,               -- datetime
   sql_variant_property( getUTCDate          (), 'basetype')  getUTCDate_,            -- datetime
   sql_variant_property( sysDateTime         (), 'basetype')  sysDateTime_,           -- datetime2
   sql_variant_property( sysUTCDateTime      (), 'basetype')  sysUTCDateTime_,        -- datetime2
   sql_variant_property( sysDateTimeOffset   (), 'basetype')  sysDateTimeOffset_,     -- datetimeoffset
   sql_variant_property( current_timestamp     , 'basetype')  current_timestamp_      -- datetimeoffset

Index