AnswerBun.com

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

Database Administrators Asked on January 4, 2022

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)

2 Answers

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.

Answered by Henrico Bekker on January 4, 2022

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.

Answered by Evan Carroll on January 4, 2022

Add your own answers!

Related Questions

Automatically purging binary logs

6  Asked on January 6, 2022 by kriegu

 

Query customers attributes

1  Asked on January 6, 2022 by mac-portter

         

MongoDB different members of cluster choosing different index

1  Asked on January 4, 2022 by marc-esher

   

The opposite of ordering by a column in SQL

2  Asked on January 2, 2022 by john-hamelink

       

How to deal with a person that may have multiple alias names?

2  Asked on January 2, 2022 by javascriptloser

   

Service Broker locks

1  Asked on January 2, 2022

   

How can I find a view column’s base table column?

3  Asked on December 31, 2021 by durette

       

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir