Hugo Kornelis (3/21/2012)

---

Nice question on a little known (and, frankly, barely useful) feature.
Too bad it is hampered by the all too common mistake of depending on a time format. In some regions of the world, the test table has data for January 1st, February 1st, March 1st, and May 1st.
#FeelingLikeBrokenRecord

venoym (3/21/2012)

---

Hugo, According to my reading... this question doesn't care which date format (other than the answer that specifies Jan 3rd) it is using. In most, if not all, regions I would suppose that it would return all dates.

KWymore (3/21/2012)

---

I had only looked at the ANY and SOME syntax once before and did not see a need for them. They seem to perform exactly like the IN statement unless I am missing something. Are there any instances where you could not perform the same logic using IN instead of ANY?

SELECT  *
FROM    ( VALUES ( 1), ( 2), ( 3) ) AS MyQuery (MyCol)
WHERE   MyCol > ANY ( SELECT    MyCol2
                      FROM      ( VALUES ( 2), ( 3) ) AS MyQuery2 (MyCol2) ) ;

KWymore (3/21/2012)

---

Are there any instances where you could not perform the same logic using IN instead of ANY?

Hugo Kornelis (3/21/2012)

---

Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.

GSquared (3/21/2012)

---

There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.