Doing all the logic in SQL requires more complexity using subqueries.
It gets uglier if you need to find the contract valid on a certain date based off of a join.
These issues can be covered up with code as it will a easier to have reusable functions, but it makes the job of a data analysts much more difficult and error prone.
Uglier than having to find the record after the one you're inserting (so you can determine your new record's end date from the subsequent record's start date) and the record before (so you can modify its end date to match your new record's start date)?
It gets uglier if you need to find the contract valid on a certain date based off of a join.
These issues can be covered up with code as it will a easier to have reusable functions, but it makes the job of a data analysts much more difficult and error prone.