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 October 28, 2021 by gunnar
1 Asked on October 28, 2021 by vivvvi
mysql mysql 5 0 mysql 5 1 performance query performance slow log
0 Asked on October 28, 2021 by jarrette
2 Asked on October 28, 2021
1 Asked on October 28, 2021 by user9516827
1 Asked on October 28, 2021 by rainer-yuan
2 Asked on October 28, 2021 by jshark
execution plan index tuning optimization query performance sql server
2 Asked on October 28, 2021 by rajnikant-sharma
1 Asked on October 28, 2021 by dean-kayton
0 Asked on October 28, 2021 by 2alltra8034
0 Asked on October 28, 2021 by wavrik
1 Asked on October 28, 2021 by pantea
0 Asked on October 27, 2021 by miyuki-narahara
2 Asked on October 27, 2021 by chessbrain
Get help from others!
Recent Answers
Recent Questions
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP