1

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.

Ram
  • 47
  • 5
  • 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 Oct 08 '18 at 02:18
  • 2
    Obligatory: scalar valued functions are often the cause of performance problems.... – Mitch Wheat Oct 08 '18 at 02: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 Oct 08 '18 at 02:35
  • [SQL Server does not have a boolean data type](https://stackoverflow.com/a/41695199/3094533). A bit is not a boolean, but the closest living relative (sort of speak) of it. – Zohar Peled Oct 08 '18 at 05:40

1 Answers1

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

Squirrel
  • 23,507
  • 4
  • 34
  • 32
  • ...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. – Zohar Peled Oct 08 '18 at 05:38