How to SELECT year using a WHERE clause on a DateTime type column

Question

There is a table Saleswith data from year 2005 to year 2015 I want to query data and filter column Sales_Date with datetime data type where the year is 2013 including all other columns

     SELECT *
     FROM Sales
     WHERE Sales_Date BETWEEN '2013-01-01' AND '2014-01-01';

is this correct or there is a function specifically to filter query by year of datetime data type

Answer 1

It is not correct as it includes data from 2014-01-01, which isn't a day in 2013.

Instead of between, which checks for a closed interval, use >= and < to search for an interval open at the end.

SELECT *
       FROM sales
       WHERE sales_date >= '2013-01-01'
             AND sales_date < '2014-01-01';

You could also use year() or datepart() to extract the year like in

...
WHERE year(sales_date) = 2013;

or

...
WHERE datepart(year, sales_date) = 2013;

But that will prevent any index on sales_date to be used, which isn't good in terms of performance. So it's not the best way to query that data, the first approach with >= and < is to be preferred.