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

To get the current date and time in SQL Server current date, use the GETDATE() function. This function returns a datetime data type; in other words, it contains both the date and the time, e.g. 2019-08-20 10:22:34 .

Or you can use, 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_SysDateTime2018-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()});

The built-in functions are recommended over ODBC scalar functions.