April 8, 2014 at 8:34 am
I Have a question for all you t-sql experts. I made a type in my code that I just noticed today. Instead of >= I did a >+ and the script runs fine with no syntax errors. There's no mention of this operator in BOL. We were discussing it here and we were thinking it might do some sort of boolean concatenation. Can anyone shed some light on this? I've fixed my typo, so it's not critical, I'm just really curious.
April 8, 2014 at 8:40 am
What did the script look like? I am not familiar with that one but it must have something to do with context because I get a syntax error every way I tried to use that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 8:41 am
Nothing. It's not an operator. However + designates a positive numeric value (like - designates a negative number), it's optional, but it's allowed and spaces are permitted between the + and the number. Sooo....
WHERE A > -1
WHERE A >-1
WHERE A >- 1
WHERE A > +1
WHERE A >+1
WHERE A >+ 1
Following?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2014 at 8:42 am
Oops, sorry, I meant to post the code too...
I have a date field called WithdrawlDate the code I had was
WithDrawalDate >+ GETDATE()
April 8, 2014 at 8:45 am
WithDrawalDate >+ GETDATE()
meaning
WithDrawalDate > +GETDATE()
and since + is the default
WithDrawalDate > GETDATE()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2014 at 8:47 am
Thanks gail, I was hoping it would do something really cool in the right context. But now that you explain it, of course it makes perfect sense.
April 9, 2014 at 12:55 pm
According to my findings (observed behavior) , using the minus "-" bypasses (for the lack of better word) the logical operator, select * from table where x >- y brings everything back. The same query with >+ respects the operator. I can see some interesting possibilities, 0x2b, 0x2d... :unsure:
April 9, 2014 at 1:08 pm
Eirikur Eiriksson (4/9/2014)
According to my findings (observed behavior) , using the minus "-" bypasses (for the lack of better word) the logical operator, select * from table where x >- y brings everything back. The same query with >+ respects the operator. I can see some interesting possibilities, 0x2b, 0x2d... :unsure:
Your findings are consistent with the explanation Gail gives above. I doubt that the negative sign is telling SQL Server to ignore the operator. It's more likely that in your case x > (-y) for all records in the table. This would always be true if the values are nonnegative integers. Did you test with negative values?
April 9, 2014 at 1:15 pm
dj341 (4/9/2014)
Eirikur Eiriksson (4/9/2014)
According to my findings (observed behavior) , using the minus "-" bypasses (for the lack of better word) the logical operator, select * from table where x >- y brings everything back. The same query with >+ respects the operator. I can see some interesting possibilities, 0x2b, 0x2d... :unsure:Your findings are consistent with the explanation Gail gives above. I doubt that the negative sign is telling SQL Server to ignore the operator. It's more likely that in your case x > (-y) for all records in the table. This would always be true if the values are nonnegative integers. Did you test with negative values?
:w00t: you are right, it simply negates the right hand side value 🙂
(donk, operator precedence...)
April 9, 2014 at 1:16 pm
Eirikur Eiriksson (4/9/2014)
According to my findings (observed behavior) , using the minus "-" bypasses (for the lack of better word) the logical operator, select * from table where x >- y brings everything back.
No.
CREATE TABLE #Test (
NumINT
)
INSERT INTO #Test (Num)
VALUES (-5), (-4), (-3), (-2), (-1), (0)
GO
DECLARE @y INT = 1;
-- returns only the 0 row
SELECT * FROM #Test AS t WHERE Num >-@y
-- because, it's equivalent to
SELECT * FROM #Test AS t WHERE Num > (-1*@y)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2014 at 1:18 pm
GilaMonster (4/9/2014)
Eirikur Eiriksson (4/9/2014)
According to my findings (observed behavior) , using the minus "-" bypasses (for the lack of better word) the logical operator, select * from table where x >- y brings everything back.No.
CREATE TABLE #Test (NumINT
)
INSERT INTO #Test (Num)
VALUES (-5), (-4), (-3), (-2), (-1), (0)
GO
DECLARE @y INT = 1;
-- returns only the 0 row
SELECT * FROM #Test AS t WHERE Num >-@y
-- because, it's equivalent to
SELECT * FROM #Test AS t WHERE Num > (-1*@y)
Thanks:-)
April 9, 2014 at 1:18 pm
Eirikur Eiriksson (4/9/2014)
:w00t: you are right, it simply negates the right hand side value 🙂(donk, operator precedence...)
One of those days?
April 9, 2014 at 1:20 pm
dj341 (4/9/2014)
Eirikur Eiriksson (4/9/2014)
:w00t: you are right, it simply negates the right hand side value 🙂(donk, operator precedence...)
One of those days?
No better way of putting it!:-D
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply