mysql get rows between two dates based on two columns

Question

My table have structure like this

id | date_birth | date_marriage
1  | 1970-01-24 | 2000-05-14
2  | 1979-05-08 | 2008-08-12

How to get events between two dates for above data in mysql (date comparison is irrespective of year) . For example if start_date is 2014-05-01 and end date is 2014-05-20 then output should be

id | event
1  | married on 2000-05-14
2  | born on 1979-05-08

UPDATE: If both date of marriage and birth date falls in the supplied date range for same person the output should contains two columns for the person

( sorry for my bad english )

Can you prepare SQLfiddle?

user4035
– user4035

2014-09-20 18:23:33 +00:00
Commented Sep 20, 2014 at 18:23 — user4035
– user4035, Commented Sep 20, 2014 at 18:23
@user4035 sqlfiddle.com/#!2/11286

dInGd0nG
– dInGd0nG

2014-09-20 18:42:15 +00:00
Commented Sep 20, 2014 at 18:42 — dInGd0nG
– dInGd0nG, Commented Sep 20, 2014 at 18:42

Andomar · Accepted Answer · 2014-09-20 18:45:40Z

1

In response to your comment, you can get two rows if either date matches by using a union.

You can check the date regardless of year by converting to a format that does not include the year, for example %m%d. Note that the format should be sortable (most significant number first) for the comparison to work.

select  *
from    YourTable
where   date_format(date_birth,'%m%d') between '0501' and '0520'
union all
select  *
from    YourTable
where   date_format(date_marriage,'%m%d') between '0501' and '0520'

edited Sep 20, 2014 at 18:45

answered Sep 20, 2014 at 18:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dInGd0nG Over a year ago

What if date of birth and date of marriage falls in same month? I want it as two rows then

Collectives™ on Stack Overflow

mysql get rows between two dates based on two columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related