Incorrect syntax near '>'

Question

I'm receiving the above error when trying to create a function in SQL Server.

I've tried the suggested solution in this question with no luck. I've also tried removing the '>' (so it's just '=') and the error switches to the '='. I have Redgate Tools installed, and I originally used a code snippet to generate the Create Function text. However, even if I start a new blank query file and type out the text manually, I get the same error. I've tried changing the data type of the variable. I'm stumped. I can't find anything actually wrong with the syntax.

create function [dbo].[is_valid_date] (@date sql_variant)
returns bit
as
begin
declare @ret bit;

select @ret = (
        @date >= '1753-01-01'
        and @date <= '9999-12-31:23:59:59.9999'
        );

return @ret;
end
go

Any suggestions?

SQL Server supports the [bit](https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017) data type. (Valid values are 0, 1, 'TRUE' and 'FALSE'.) There is a [boolean](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql?view=sql-server-2017#boolean-data-type) data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one: "Unlike other SQL Server data types, a **Boolean** data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." — HABO, Aug 22 '19 at 17:57

Gordon Linoff · Answer 1 · 2019-08-22T17:29:03.113

4

Use a case. SQL Server does not support boolean expressions as first-class values:

select @ret = (case when @date >= '1753-01-01' and @date <= '9999-12-31:23:59:59.9999'
                    then 1 else 0
               end);

return @ret;

Note: You can just return the expression. There is no need to assign the case to a variable.

edited Aug 22 '19 at 17:29

answered Aug 22 '19 at 17:27

Gordon Linoff

1,242,037
58
646
786

That did it. Thanks. – Mike Caputo Aug 22 '19 at 17:28
As for assigning it to a variable, that was another thing I tried to get rid of the error, forgot to mention that. – Mike Caputo Aug 22 '19 at 17:30

score 0 · Answer 2 · answered Aug 22 '19 at 17:32

0

You can always do

 set @ret = 0
 IF @date >= '1753-01-01' AND @date <= '9999-12-31:23:59:59.9999'
 set @ret = 1

answered Aug 22 '19 at 17:32

Gaspa79

5,488
4
40
63

Incorrect syntax near '>'

2 Answers2