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

How to log into a fresh MariaDB install?

2  Asked on October 28, 2021

   

Query tables with information_schema in pgsql

1  Asked on October 28, 2021 by boodoo

 

Designing golf pro scheduling into POS

0  Asked on October 28, 2021 by jarrette

   

MariaDB Galera + Remote Backup

1  Asked on October 28, 2021

     

Query is failing

2  Asked on October 27, 2021

     

PostgreSQL resources requirements

0  Asked on October 27, 2021 by miyuki-narahara

     

Optimizing my query

2  Asked on October 27, 2021 by kilazur

     

When (or why even) use PLPython(3)u

2  Asked on October 27, 2021 by chessbrain

     

Ask a Question

Get help from others!

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