What is the CURRENT_DATE or current date value function for SQL Server?

  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 recognizes CURRENT_DATE (SQL server today’s 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()});
The built-in functions are recommended over ODBC scalar functions.