comparing dates is failing in SQL function

Question

I am trying to configure a computed column using Scalar function. I need to set a BIT on column status based on dates.

CREATE FUNCTION dbo.setStatus(@StartDate datetime, @EndDate datetime)
RETURNS bit
AS
BEGIN 
  RETURN (@StartDate < GETDATE() && GETDATE() < @EndDate)
END 
GO

I am seeing error in ssms that the symbol "<" is invalid.

do note that getdate() returns current date & time. If you only required today's date, use use convert or cast it to date data type convert(date, getdate()) — Squirrel
– Squirrel, Commented Oct 8, 2018 at 2:18
Obligatory: scalar valued functions are often the cause of performance problems.... — Mitch Wheat
– Mitch Wheat, Commented Oct 8, 2018 at 2:25
Mm, one of the ways in which SQL syntax differs from a programming language is that you can't return the result of a boolean expression. You can use boolean data types to test whether something is true (e.g. where clauses and case expressions) but not return the boolean data type itself. Also, it works slightly different than you might expect since booleans can evaluate to true, false or unknown (it's not a simple 1 or 0 when nulls are involved). Essentially, you can't use true/false for bitwise operations like you might expect. — ZLK
– ZLK, Commented Oct 8, 2018 at 2:35
SQL Server does not have a boolean data type. A bit is not a boolean, but the closest living relative (sort of speak) of it. — Zohar Peled
– Zohar Peled, Commented Oct 8, 2018 at 5:40

Squirrel · Accepted Answer · 2018-10-08 07:04:28Z

4

you need to use a CASE statement to check the condition and return 1 or 0 accordingly

CREATE FUNCTION dbo.setStatus(@StartDate datetime, @EndDate datetime)
RETURNS bit
AS
BEGIN 
  RETURN (CASE WHEN @StartDate < GETDATE() AND GETDATE() < @EndDate THEN 1 ELSE 0 END)
END 
GO

EDIT : the logical AND operator for SQL Server is AND and not &&. I have make that change in the query

edited Oct 8, 2018 at 7:04

answered Oct 8, 2018 at 2:24

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zohar Peled Over a year ago

...And of course change the && to AND (that I see you did but didn't mention that in your explanation. Of course, as Mitch Wheat wrote in the comments to the answer - This is likely to be a performance killer.

Collectives™ on Stack Overflow

comparing dates is failing in SQL function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related