MySQL date between won't get desired result

Question

i've got a weird case. It's annoyingly easy to solve but I can't figure it out.

My goal was storing the history of some racing drivers' transfers.

I have this table with example data: table's name is contratti (contracts)

id | start | end | pilot_id | team_id | role 1 | 2019-09-05 | 2019-09-17 | 1 | 10 | 1 2 | 2019-09-17 | 2020-04-01 | 1 | 3 | 1 3 | 2020-04-02 | NULL | 1 | 9 | 2

Basically it contains every pilot&team contract with his start and finish date, and other minor details.

If the end field is set to null, then it means that there's no end date published yet.

My goal is to get through a query (with a pilot_id and a date) the corresponding team_id of that time.

eg: given pilot_id as 1, and date as 2020-05-14, system would return 9.

eg2: given pilot_id as 1, and date as 2019-12-25, system would return 3.

I know it is fairly easy to do, but it's about an hour i'm trying and I can't solve this. Thanks for your time.

Gordon Linoff · Accepted Answer · 2020-05-14 14:53:28Z

2

Basically you need an or comparison:

select t.* from t where t.pilot_id = :pilot_id and :date >= t.start and (:date <= t.end or t.end is null)

answered May 14, 2020 at 14:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Giovanni Rizza Over a year ago

Thanks gordon, i basically did the same previously, but probably got some wrong/messed up syntax.

Collectives™ on Stack Overflow

MySQL date between won't get desired result

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related