Upper limit of the Year condition in my query is ignored

user_4623674 0
Viewed 116

This is quite an embarrassing question but it's wasted 2 hours of my time, so I am giving up.

In below query, the second condition (which sets the upper limit of my query) is ignored by SQL Server. It returns ALL years greater than 2018, instead of returning rows from 2018 to 2021 (assuming today's year is 2020).

Please note that I would like to KEEP the years, and control this using YEARS, and not provide datetime. what am I doing wrong? why is my query returning all rows greater than 2018 (upper limit is ignored)??? 

                    --THIS QUERY SHOULD RETURN ALL ROWS WITH "STARTDATETIME"
                    -- WITH YEARS GREATER THAN 2018 (SO BASICALLY 2018-01-01) 
                    -- BUT NOT THE ROWS WITH YEARS GREATER THAN ONE YEAR AHEAD OF TODAY'S DATE 
                    -->STARTDATETIME IS DATETIME
                    --I'D LIKE TO MANAGE THIS QUERY BY USING YEARS (BECAUSE IT IS A PARAM IN SSRS)
                    SELECT STARTDATETIME FROM ACTION
                    WHERE  (YEAR(STARTDATETIME)>='2018' --Greater than equal to 2018  
                            AND 
                            (YEAR(STARTDATETIME)<=(DATEADD(year, 1, GETDATE()))) --this condition is mysteriously ignored 
                            -- I kept adding brackets. 
                            ) --but up to only one year ahead
                    ORDER BY STARTDATETIME DESC

What did I try? Everything imaginable (except giving actual datetime). I kept adding brackets to solve the issue, but it didn't help

sql-server-2012 datetime sql-server sql
3 Answers

YEAR() returns an integer.

DATEADD() returns a date.

These are not comparable.

Perhaps you intend:

STARTDATETIME <= DATEADD(year, 1, GETDATE())

If you want through the end of next year, I would recommend:

STARTDATETIME < datefromparts(year(getdate()) + 2, 1, 1)

That is, before the first day of 2022. This works for both dates and date/times. And, it allows indexes to be used, if available.

user_11440350

You are comparing int to date-time :

YEAR(STARTDATETIME) = DATEADD(year, 1, GETDATE())

You either need to call year() with dateadd() or remove year() from STARTDATETIME :

WHERE YEAR(STARTDATETIME) >= 2018 AND
      YEAR(STARTDATETIME) <= YEAR(DATEADD(year, 1, GETDATE()))
user_55294450

On your second condition, you're comparing an year to an entire date:

(YEAR(STARTDATETIME)<=(DATEADD(year, 1, GETDATE())))

Why don't you try the following instead?

STARTDATETIME<=DATEADD(year, 1, GETDATE())
user_97738370
Related