What is the use of the hyphen before a function in SQL (SQLServer)

Question

I would like to find out what the logic behind the SQL Syntax is when including a hyphen before calling a SQL function enclosed in parentheses.

Here is the SQL:

IF (@StartDate > @EndDate)
BEGIN
    SET @EndDate = @StartDate
    SET @StartDate = @EndDate
END

DECLARE @nonworkingweekdays int

--now deal with public holidays
SELECT @nonworkingweekdays = count("Date") from 
(
    select distinct
    (
    CASE datepart(weekday,date)
            WHEN 1 THEN null --ignore sundays   
            WHEN 7 THEN null --ignore saturdays
            else "Date"
    END
    ) AS "date" 
    from publicholidays
) nonworkingweekdays 
WHERE 
"Date" is not null and 
"Date" between @StartDate and DATEADD(day, -1, @EndDate)

RETURN
    CASE WHEN @StartDate <= @EndDate
    THEN
        dbo.FullWeekDays(@StartDate, @EndDate) - @nonworkingweekdays
    ELSE
        -(dbo.FullWeekDays(@StartDate, @EndDate) - @nonworkingweekdays)
    END

The logic I am confused about is in the else statement with the return statement at the bottom of this script.

Thanks in advance :)

It's the same as multiplying with -1, look at the CASE condition that is probably the reason why a negative value is returned — Joakim Danielson, Apr 02 '19 at 13:43
The - is not in front of the function call actually...and it's outside the bracket too. So the - is applied to the _result_ of the function minus the value of @nonworkingweekdays (I.e the result of the calculation inside the brackets). So assuming the function returns a number, then the - will simply negate it. E.g. if the operation in the brackets returns 4 then the final result output by the else condition will be -4. — ADyson, Apr 02 '19 at 13:44
BTW, `DISTINCT` is not a function, it's a part of `SELECT DISTINCT` and works on the whole selected rows. Simply do `select distinct CASE datepart(weekday,date) ...` to make code clearer! — jarlh, Apr 02 '19 at 13:45
It means someone isn't paying attention to their code since a) they could just use [`ABS`](https://learn.microsoft.com/en-us/sql/t-sql/functions/abs-transact-sql?view=sql-server-2017) even if it were necessary, which it isn't since b) The logic at the top means that the `ELSE` clause is never considered. — Damien_The_Unbeliever, Apr 02 '19 at 13:52
and c) the logic at the top has never been tested since it doesn't *swap* the values of the two variables, it just ends up with both set to whatever value `@StartDate` has before this code runs. — Damien_The_Unbeliever, Apr 02 '19 at 13:57
Note the [`ABS`](https://learn.microsoft.com/en-gb/sql/t-sql/functions/abs-transact-sql?view=sql-server-2017) inbuilt function which avoids branching in cases like this. — Richard, Apr 02 '19 at 14:11

score 5 · Accepted Answer · answered Apr 02 '19 at 13:45

5

It's the TSQL Unary Negative Operator:

Returns the negative of the value of a numeric expression (a unary operator). Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category.

answered Apr 02 '19 at 13:45

David Browne - Microsoft

80,331
6
39
67

score 1 · Answer 2 · answered Apr 02 '19 at 14:23

1

It is a unary negation operator, so the same as (0 - <expression>).

That said, I am guessing that this is more simply expressed as:

RETURN ABS(dbo.FullWeekDays(@StartDate, @EndDate) - @nonworkingweekdays)

answered Apr 02 '19 at 14:23

Gordon Linoff

1,242,037
58
646
786

But as I noted in the comments, look at the logic at the top of the code. `ELSE` was unused anyway... – Damien_The_Unbeliever Apr 02 '19 at 17:46

score 0 · Answer 3 · edited Apr 02 '19 at 14:08

The hyphen is there to reverse the sign of the expression's value, because in the case you have there:

CASE WHEN @StartDate <= @EndDate
THEN
    dbo.FullWeekDays(@StartDate, @EndDate) - @nonworkingweekdays
ELSE
    -(dbo.FullWeekDays(@StartDate, @EndDate) - @nonworkingweekdays)
END

you are returning the difference between the dates minus the nonworkingdays variable.

it is logically the best option, because you cant have a -1 days difference, you always need a positive integerin the difference.

be sure to make a way to differentiate between the types of return (when the startdate is greater than enddate and vice-versa)

What is the use of the hyphen before a function in SQL (SQLServer)

3 Answers3