CODEWITHSUNDEEP
sql-servert-sqlsql-function
Ram
Ram

Reputation: 47

comparing dates is failing in SQL function

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.

Upvotes: 1

Views: 161

Answers (1)

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 4

Related Questions