Daniel Daniel - 24 days ago 10
SQL Question

How to select combinations of years and months in SQL query?

I need some help with a SQL query where I need to find all possible combinations of years and months that interest me. Here is my take on it where the

o.orderdate
is of type
DATETIME
.

SELECT
o.orderdate
FROM
Sales.Orders o
WHERE
(YEAR(o.orderdate) IN (2007, 2008))
AND (MONTH(o.orderid) IN (1, 2, 3))
ORDER BY
o.orderdate;


What I get is as a result is this where we can see that the months are not selected.

2007-07-08 00:00:00.000
2007-07-09 00:00:00.000
2007-07-09 00:00:00.000
2007-07-10 00:00:00.000
2007-07-11 00:00:00.000
2007-07-11 00:00:00.000
2007-07-14 00:00:00.000
2007-07-15 00:00:00.000
2007-07-16 00:00:00.000
2007-07-16 00:00:00.000
2007-07-17 00:00:00.000
2007-07-18 00:00:00.000
2007-07-18 00:00:00.000
2007-07-21 00:00:00.000
2007-07-22 00:00:00.000
2007-07-22 00:00:00.000
2007-07-23 00:00:00.000
2007-07-24 00:00:00.000
2007-07-25 00:00:00.000
2007-07-25 00:00:00.000
2007-07-28 00:00:00.000
2007-07-29 00:00:00.000
2007-07-29 00:00:00.000
2007-07-30 00:00:00.000
2007-07-31 00:00:00.000
2007-07-31 00:00:00.000
2007-08-01 00:00:00.000
2007-08-04 00:00:00.000
2007-08-05 00:00:00.000
2007-08-05 00:00:00.000
2007-08-06 00:00:00.000
2007-08-07 00:00:00.000
2007-08-07 00:00:00.000
2007-08-08 00:00:00.000
2007-08-11 00:00:00.000
2007-08-11 00:00:00.000
2007-08-12 00:00:00.000
2007-08-12 00:00:00.000
2007-08-13 00:00:00.000
2007-08-14 00:00:00.000

Answer

You have put the wrong parameter into the MONTH() function it should be

SELECT
  o.orderdate
FROM Sales.Orders o
WHERE (YEAR(o.orderdate) IN (2007, 2008))
  AND (MONTH(o.orderdate) IN (1, 2, 3))
 ORDER BY o.orderdate;

Alternatively, if you are selecting for dates in a date range it is best practice to use inequalities with the full date

SELECT
  o.orderdate
FROM Sales.Orders o
WHERE (o.orderdate < '2007-01-01' AND o.orderdate < '2007-03-01')
  AND (o.orderdate < '2008-01-01' AND o.orderdate < '2008-03-01')
 ORDER BY o.orderdate;
Comments