sqlsql-serverdatabasedatetimeselect

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

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

Solution

  • 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.