CODEWITHSUNDEEP
mysqlsqldatabase
Mahavir Munot
Mahavir Munot

Reputation: 1464

mysql - select rows between start year and start month to end year and end month?

I am trying to select the rows for given range of year and month. (i.e.: from <start year>+<start month> to <end year>+<end month>) I tried the below query but I am getting unexpected rows. Am I missing something?

SELECT * FROM table AS t
WHERE 
((YEAR(t.column1)='<start year>' AND MONTH(t.column1)>='<start month>') OR
(YEAR(t.column1)>'<start year>' AND YEAR(t.column1)<'<end year>') OR
(YEAR(t.column1)='<end year>' AND MONTH(t.column1)<='<end month>'))

Upvotes: 2

Views: 3303

Answers (3)

Omesh
Omesh

Reputation: 29121

You may try something like this:

SELECT * FROM table AS t
WHERE  t.column1
       BETWEEN STR_TO_DATE(CONCAT('<start year>', '<start month>', '01'), '%Y%m%d') AND 
               LAST_DAY(STR_TO_DATE(CONCAT('<start end>', '<start end>','01'), '%Y%m%d'));

This will should perform better, as it will use index from column column1.

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174457

Your query only works correctly if start year and end year are different. If they are the same the query returns all rows from that year. You need to handle this case seperatly:

(
    start_year != end_year and 
    (
        (YEAR(t.column1)='<start year>' AND MONTH(t.column1)>='<start month>') OR  
        (YEAR(t.column1)>'<start year>' AND YEAR(t.column1)<'<end year>') OR  
        (YEAR(t.column1)='<end year>' AND MONTH(t.column1)<='<end month>')
    )
)
OR (start_year = end_year and MONTH(t.column1) >= start_month
                          and MONTH(t.column1) <= end_month)

However, this can be greatly simplified:

YEAR(t.column1) * 12 + MONTH(t.column1) >= start_year * 12 + start_month
and YEAR(t.column1) * 12 + MONTH(t.column1) <= end_year * 12 + end_month

or even shorter with between:

YEAR(t.column1) * 12 + MONTH(t.column1)
BETWEEN start_year * 12 + start_month and end_year * 12 + end_month

Upvotes: 5

pQd
pQd

Reputation: 126

try this:

... WHERE t.column1>="startYear-startMonth-01" AND t.column1<="endYear-endMonth-31"

Upvotes: 1

Related Questions