GETDATE (Transact-SQL)

Getting the current system date and time

SELECT SYSDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE();

Getting the current system date

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE());

Getting the current system time

SELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE());