CODEWITHSUNDEEP
mysqlsql
Giovanni Rizza
Giovanni Rizza

Reputation: 49

MySQL date between won't get desired result

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.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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)

Upvotes: 2

Related Questions