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)
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
1 Asked on January 6, 2022
1 Asked on January 6, 2022 by franz-huebner
1 Asked on January 6, 2022 by mac-portter
1 Asked on January 4, 2022 by marc-esher
1 Asked on January 4, 2022 by lares-dk
2 Asked on January 4, 2022
2 Asked on January 4, 2022 by crashmeister
2 Asked on January 2, 2022 by john-hamelink
2 Asked on January 2, 2022 by javascriptloser
1 Asked on January 2, 2022 by james-randall
1 Asked on January 2, 2022
0 Asked on January 2, 2022 by gandalf
1 Asked on January 2, 2022 by atmdev
3 Asked on December 31, 2021 by durette
1 Asked on December 31, 2021 by triffids
1 Asked on December 31, 2021 by j-brune
Get help from others!
Recent Answers
Recent Questions
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir