Date and Time Functions

SYSDATETIME, SYSDATETIMEOFFSET, and SYSUTCDATETIME functions all retrieve the current system time from the server.

GETDATE and GETUTCDATE date functions use the same format of including parentheses after the function name, the CURRENT_TIMESTAMP function does not. The CURRENT_TIMESTAMP and GETDATE functions produce the same results.

DATEPART function returns the numeric value of the part specified. For example, “1” is returned
for January.

DATEADD
SELECT DATEADD(YY, 2, GETDATE()) as NewDate, 'Add 2 years from current date' as Description

DATEDIFF syntax follows: DATEDIFF (datepart, startdate, enddate)
SELECT DATEDIFF(YEAR, GETDATE(), '1/1/2001')
SELECT DATEDIFF(DD, GETDATE(), '1/1/2009')