Selecting records greater or equal to 2021 using DATEPART in where clause but still records fo 2019,-CodePudding

   select top 10 (DATEPART (year, ss.[dateTime])) 
   from states ss
   where ((DATEPART (year, Cast(ss.[dateTime] as date))) >= (DATEPART (year, 2021)))

This returns me data with 2020, 2019 as well why?

The type of dateTime column is DATETIME and stores values like 2020-10-17 08:20:30.033.

CodePudding user response：

Honestly, don't use DATEPART on your column in there WHERE like this in the first place. Such a clause isn't SARGable. Use proper date boundaries:

SELECT TOP (10)
       DATEPART(YEAR, ss.[dateTime]) AS Year
FROM dbo.states ss
WHERE ss.[dateTime] >= '20210101'
ORDER BY {Expression(s) to order by};

If you just want to pass the year, as you're using a parameter, then use DATEFROMPARTS: >= DATEFROMPARTS(@Year,1,1)

CodePudding user response：

Change the WHERE clause and compare the result from DATEPART() with an integer value:

WHERE ((DATEPART (year, Cast(ss.[dateTime] as date))) >= 2021

Statement DATEPART (year, 2021) is an implicit conversion (2021 days after 1900-01-01) and returns 1905 (which explains the unexpected results).

An additional option (to get the year part of the [dateTime] column) is YEAR() function:

WHERE YEAR(ss.[dateTime]) >= 2021

Finally, because performance is always important, consider carefully the explanations in the @Larnu's answer.

CodePudding user response：

You should be comparing the result from DATEPART against the integer 2021:

SELECT TOP 10 DATEPART(year, ss.[dateTime])
FROM states ss
WHERE DATEPART(year, CAST(ss.[dateTime] AS date)) >= 2021;