Search code examples
sql-serversql-server-2016

i can not understand why a query filtering on datetime field is not working on sql server 2016

This is a very basic query:

select * from [dbo].[TestTable]  where year(start_date)>2021

it returns no records, start_date is datetime, the table contains many records with that field valorized and dates beyound 2021.

this query return all the records of the table:

SELECT year(start_date), * FROM [dbo].[TestTable] order by start_date desc

enter image description here

this the table structure:

enter image description here

another query with strange result:

SELECT year(start_date), case when year(start_date)>2021 then 1 else 0 end, * FROM [ADS].[dbo].[TestTable] order by start_date desc

enter image description here

what can i check?

Solution

  • You are missing the = part of the operator. All the rows seem to have 2021-01-01, so the YEAR is not greater than 2021, it IS 2021.

    You'd need to either do

    select * from [dbo].[TestTable]  where year(start_date)>=2021

    or

    select * from [dbo].[TestTable]  where year(start_date)=2021

    As Dan Guzman points out, as an additional improvement, you should avoid using functions on your WHERE clauses because that will prevent the use of indexes, since it requires to execute the function against every single row on the table to be able to determine if it's a match.

    If it's a small table in terms of record count, it's not a big deal, but if you're talking about tens of thousands or more, it will add up.

    The alternatives would be to either filter by the original date value, or save the year as a separate field and add an index to it.