SQL Server – CURRENT_DATE or Current Date Value Function

datesql server

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)

Best Answer

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.

Related Solutions

SQL Server – How to Get Current Date Without Time Part?

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.

Sql-server – Column definition for a SQL Server equivalent to Access BOOLEAN type

To address your concerns about BIT:

You can set your BIT column to NOT NULL.
You can use -1 when setting a BIT column to "true".
You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language).

CREATE TABLE dbo.foo(bar BIT NOT NULL, blat BIT NOT NULL);

INSERT dbo.foo SELECT -1, 0;

SELECT bar, blat, -CONVERT(SMALLINT, bar), -CONVERT(SMALLINT, blat) FROM dbo.foo;

Results:

bar   blat   
----  ----  ----  ----
1     0     -1    0

The nice thing about BIT over TINYINT/SMALLINT is that if you have between 1-8 BIT columns, they can fit into a single byte.

In all of these cases, you still aren't going to be able to say

WHERE NOT BooleanColumn
-- or
WHERE !BooleanColumn

You will still have to say

WHERE BooleanColumn = 0

Best Answer

Related Solutions

SQL Server – How to Get Current Date Without Time Part?

Sql-server – Column definition for a SQL Server equivalent to Access BOOLEAN type

Related Question