CODEWITHSUNDEEP
sqlsql-serverdatabase
Zuher Abud Said
Zuher Abud Said

Reputation: 745

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

Upvotes: 4

Views: 66829

Answers (2)

sticky bit
sticky bit

Reputation: 37497

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.

Upvotes: 12

Mureinik
Mureinik

Reputation: 312257

You can use the year function:

SELECT *
FROM   Sales
WHERE  YEAR(Sales_Date) = 2013;

Upvotes: 1

Related Questions