1

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)
5

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.

3

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.

  • This is the way I have always done it, although if you are casting to DATE, does it matter if the input time has precision to the nanosecond? – Jonathan Fite Jun 30 '18 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.