0

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.

  • In other words, given dates S and C, you want to subtract one year from date S iteratively until it falls before date C (unless it's already before date C), correct? On a different note, what do you mean by "best" in "what would be the best way…"? – Andriy M Nov 21 '16 at 12:25
  • @AndriyM Yes, that's essentially what I want to do. I suppose by best I mean the least hacky way of doing it. Having read your simpler way of expressing it, it's occurred to me that what I need to do is take the day and month of date S and append the year of date C - if this new date is before date C return it, otherwise return the day and month of date S with the year of date C - 1. – Gareth Nov 21 '16 at 13:15
  • Feel free to answer with your own solution if/when you've figured out one. – Andriy M Nov 21 '16 at 13:35
1

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.

  • This isn't quite the solution I used (mine's complicated by the fact that we have dd/mm/yyyy dates in the claim table and unix timestamps in the policy table - oh how I wish we'd used DATE from the start), but I'm marking it as accepted as it's very helpful. – Gareth Dec 1 '16 at 9:27
  • If I understand you correctly, all you need to do is replace claim_date with STR_TO_DATE(claim_date, '%d/%m/%Y'). – Rick James Dec 1 '16 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.