You can use either [`GETDATE`][1] (return type [`datetime`][2]) or [`SYSDATETIME`][3] (return type [`datetime2`][4]), with the difference being the precision up to nanoseconds for `SYSDATETIME()`.

Example: 

    SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

Results:

<pre>
<b>fn_GetDate</b>	              <b>fn_SysDateTime</b>
2018-06-27 10:31:18.963	   2018-06-27 10:31:18.9659170</pre>

See [Date and Time Data Types and Functions (Transact-SQL)][5] in the product documentation.

---

For completeness, SQL Server also recognises `CURRENT_DATE` as mentioned in the question, as an [ODBC scalar function][6]:

    SELECT {fn CURRENT_DATE()};

This returns `varchar(10)`, so would need an explicit cast or convert to the [`date`][7] data type:

    SELECT CONVERT(date, {fn CURRENT_DATE()}, 23);

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

  [1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017
  [2]: https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
  [3]: https://learn.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-2017
  [4]: https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017
  [5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017
  [6]: https://learn.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql?view=sql-server-2017
  [7]: https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-2017