You can use either GETDATE (return type datetime) or SYSDATETIME (return type datetime2), with the difference being the precision up to nanoseconds for SYSDATETIME().
Example:
SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime
Results:
fn_GetDate fn_SysDateTime 2018-06-27 10:31:18.963 2018-06-27 10:31:18.9659170
See Date and Time Data Types and Functions (Transact-SQL) in the product documentation.
For completeness, SQL Server also recognises CURRENT_DATE as mentioned in the question, as an ODBC scalar function:
SELECT {fn CURRENT_DATE()};
This returns varchar(10), so would need an explicit cast or convert to the date data type:
SELECT CONVERT(date, {fn CURRENT_DATE()}, 23);
The built-in functions are recommended over ODBC scalar functions.
