Mysql – Most recent fixed date (i.e. 4th July) before given date

dateMySQL

Given a fixed date, for instance 27/04, what would be the best way to get the most recent year that the aforementioned date happened before another given date?

Some examples:

Date A: 27/04
Date B: 25/05/2016
Result: 27/04/2016

Date A: 27/04
Date B: 25/04/2016
Result: 27/04/2015

A bit of background as to why we need this:
I'm correcting an issue in a report to do with insurance claims for the broker I work for. The report joins a few tables, but the two relevant ones to this question are policies which stores the start date of a policy, and reportedclaims which stores the claims that have been reported while the client has been insured with us.
What we need is the start date of the most recent policy year.
Each year when a policy is renewed its start date is advanced by one year. This means that when we run this report the start date of the policy may be after the claim date, which makes no sense. We don't store the original start date, but even if we did we'd have a similar problem if a claim came in 3 or 4 years after the policy was first incepted:

Original start date: 27/04/2012
Current start date: 27/04/2016
Claim date: 26/04/2016
Required start date: 27/04/2015

I could do the calculation in PHP, but since the rest of the report is being created in MySQL I'd prefer not to do that if I can avoid it.

We are currently using a column call uyear which stores the underwriting year that the claim falls under. This is ideal for the task, and saves any weighty calculations in the SQL or the script. Unfortunately I've just found out that this data is unreliable as the people entering it are quite often entering the incorrect year.

Best Answer

First, let's use MySQL's format for dates:

Start date:  xxxx-04-27
Claim date:  2016-04-26

Now we need to combine the '2016' (or 2016-1) from the Claim date to the '04-27' of some Start date. It does not matter whether it is the original or current "start date". To decide on the "-1", we need to compare both mm-dd values, which can be done as simple string.

In the following code, I am assuming DATE datatype was used; using string functions works fine. For example:

SELECT LEFT(curdate(), 4), RIGHT(curdate(), 5);
+--------------------+---------------------+
| LEFT(curdate(), 4) | RIGHT(curdate(), 5) |
+--------------------+---------------------+
| 2016               | 11-21               |
+--------------------+---------------------+

Here's the expression to generate '2015-04-27', which can be used as a DATE or in DATE_FORMAT() to format it as desired:

CONCAT(
    LEFT(claim_date, 4) -
    IF(RIGHT(claim_date, 5) <=        -- may want just '<'?
       RIGHT(start_date, 5),  1, 0),  -- whether to back up a year
    '-',
    RIGHT(start_date, 5) )  -- tack on proper 'mm-dd'

I thought about using julian dates, but leapday messes up things.

Related Question