CODEWITHSUNDEEP
mysql
Ezio Auditore
Ezio Auditore

Reputation: 169

How can show dates according a period of year?

I have a table policies with date_ini and date_expired and want to show policies according the date_ini and date expired in 2015

Here is the information:

 |date_ini|    |date_end|
 2015-01-01    2015-06-03   Here is in the period 2015
 2014-02-03    2015-09-08   Here is in the period 2015
 2015-06-03    2016-09-08   Here is in the period 2015
 2016-01-03    2016-09-08   Here is not in the period 2015
 2015-06-03    2017-09-08   Here is in the period 2015

I tried this demo:

select * from policies where YEAR(date_ini) >= 2015 AND YEAR(date_end) <= 2015

And I should have this answer:

 |date_ini|    |date_end|
 2015-01-01    2015-06-03
 2014-02-03    2015-09-08
 2015-06-03    2016-09-08
 2015-06-03    2017-09-08

Please somebody can help me with this query?

Upvotes: 0

Views: 46

Answers (1)

Rubik
Rubik

Reputation: 1471

If "date_ini" is at least always <= to date_end, it should work:

select  *
from    policies
where   2015 between YEAR(date_ini) AND YEAR(date_end)

You can also apply this to a specific date. I'm not a mysql expert, but if you want to restrict to a month, this works too:

select  *
from    policies
where   '2015-01' BETWEEN DATE_FORMAT(date_ini, '%Y-%m')
                  AND     DATE_FORMAT(date_end, '%Y-%m')

Upvotes: 1

Related Questions