Select Where Month= X and Year= Y

Question

I have the following select:

SELECT * FROM Table
WHERE Column= datepart(month,'2013-05-07 10:18:00') 
  AND Column= datepart(year,'2013-05-07 10:18:00')

I want it to show me itens where month= 05 and year= 2013. Actually it isn't showing anything (but there is results to be show). Thanks.

You're comparing both the month and year to `Column`. Are you sure you're not using the same column in your actual `WHERE` clause? — D Stanley, May 10 '13 at 13:03
Why people vote down? I did not said my code was correct. I just put what I was doing and asking for help! — Khrys, May 10 '13 at 13:14
http://stackoverflow.com/faq in "What kind of questions can I ask here?" did not ask me to read the documentation before ask a question. Stay cool, dude. — Khrys, May 10 '13 at 13:22
All is cool. But a simple read of the documentation before posting on here would have directed you on the correct path. Glad you found your answer. — Ric, May 10 '13 at 14:01

score 11 · Accepted Answer · answered May 10 '13 at 13:07

11

I would use the MONTH and YEAR functions

SELECT * FROM Table
WHERE MONTH(yourColumn) = 5  and YEAR(yourColumn) = 2013

http://msdn.microsoft.com/en-us/library/ms186313.aspx

http://msdn.microsoft.com/en-us/library/ms187813.aspx

answered May 10 '13 at 13:07

Kevin Kunderman

2,036
1
19
30

2

Definitely use this, it's better than my answer. Good job you waited those 3 minutes :-) – Andrew May 10 '13 at 13:28
@TheWaxMann No, there is actually no difference at all between them. This is translated to use `datepart`. You can see that if you look at the xml version of the queryplan. And to be honest. They are equally bad and will always have to scan all rows in the table to figure out what rows to return. There is no way this will use an index. – Mikael Eriksson May 10 '13 at 13:32

score 3 · Answer 2 · edited May 10 '13 at 13:06

3

SELECT * 
  FROM Table
 WHERE datepart(month, datecolumn) = 5
   AND datepart(year, datecolumn) = 2013

edited May 10 '13 at 13:06

Ben

51,770
36
127
149

answered May 10 '13 at 13:04

Andrew

1,963
3
25
35

You're welcome. Don't forget to mark the question as answered :-) – Andrew May 10 '13 at 13:08
yeah, SO ask me to wait some minutes... 3 to go... – Khrys May 10 '13 at 13:13

Mikael Eriksson · Answer 3 · 2013-05-10T14:03:14.230

2

Applying functions on columns is genrally a bad idea. It makes the condition non-sargable.

Better to test on an interval.

SELECT * 
FROM YourTable
WHERE DateCol >= '20130501' AND
      DateCol < '20130601'

A SQL Fiddle to play around with that proves my point

edited May 10 '13 at 14:03

answered May 10 '13 at 13:20

Mikael Eriksson

136,425
22
210
281

score -1 · Answer 4 · answered May 10 '13 at 13:04

-1

Try

SELECT * FROM Table WHERE month = DATE_FORMAT('2013-05-07 10:18:00', '%m') AND ...

answered May 10 '13 at 13:04

Thomas1703

1,152
5
16
33

2

Your answer is in MySQL; the OP is using t-sql. – Kermit May 10 '13 at 13:05

score -1 · Answer 5 · answered May 10 '13 at 13:14

I imagine your column is itself a DATE? Then you need to apply DATEPART on both sides of the "="

SELECT
        *
    FROM Table
    WHERE
        DATEPART(MONTH, Column)= DATEPART(MONTH,'2013-05-07 10:18:00') 
        AND DATEPART(YEAR, Column) = DATEPART(YEAR,'2013-05-07 10:18:00')

Select Where Month= X and Year= Y

5 Answers5