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

Question

What is the SQL Server functionality that allows you to get the current date as a DATE type like the SQL Standard's CURRENT_DATE feature. What is the <current date value function> for SQL Server?

Using PostgreSQL, I'm looking for something like,

SELECT CURRENT_DATE;
 current_date 
--------------
 2018-06-27
(1 row)

Answer 1

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()});

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

Answer 2

CAST (... to date) with GETDATE() or SYSDATETIME()

The best way is

SELECT CAST( GETDATE() AS date );

By extension in SQL Server, you can cast SYSDATETIME() to date,

SELECT CAST( SYSDATETIME() AS date );

The docs on SYSDATETIME (Transact-SQL) show some more examples,

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, SYSDATETIMEOFFSET())  
    ,CONVERT (date, SYSUTCDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE())  
    ,CONVERT (date, GETUTCDATE());  

/* All returned 2007-04-30 */  

There may be advantages CAST (GETDATE() AS date), as GETDATE() natively returns less precision.