CODEWITHSUNDEEP
mysqlsqldatecomparison
Simian
Simian

Reputation: 864

SELECT where date is within X months away regardless of stored year

I currently have a query that is getting the records where their deadline is less than 3 months away. The deadline is stored as a Date, but the year is not important as this record should flag up every year.

My query:

SELECT client_name 
FROM client 
WHERE MOD(DAYOFYEAR(deadline) - DAYOFYEAR(CURDATE()), +365) <= 90 
AND DAYOFYEAR(deadline) > DAYOFYEAR(CURDATE())

Apologies if there’s errors in the syntax as I’m writing this from memory – but it does work.

It works up until a deadline is in the first quarter and the current date is in the final quarter then it no longer returns the record. How do I get around this?

So the query needs to return the records that have a deadline within 3 months of the current date. The Year in the deadline date should be ignored as this could be years ago, but it is the day and month of the deadline that is important.

Or is the problem the date I am storing? Should I update this each year?

Thanks

Upvotes: 0

Views: 524

Answers (1)

spencer7593
spencer7593

Reputation: 108430

One approach is to use a conditional test like this:

WHERE CONCAT(YEAR(NOW()),DATE_FORMAT(d.deadline,'-%m-%d')) 
        + INTERVAL CONCAT(YEAR(NOW()),DATE_FORMAT(d.deadline,'-%m-%d'))<NOW() YEAR
    < NOW() + INTERVAL 3 MONTH

We can unpack that a little bit. On that first line, we're creating a "next due" deadline date, by taking the current year, and appending the month and day value from the deadline.

But there's a problem. Some of those "next due" deadline dates are in the past. So, to handle that problem (when the 3 month period "wraps" into the next year), we need to add a year to any "next due" deadline date that's before the current date.

Now, we can compare that to a date 3 months from now, to determine if the "next due" deadline date is in the next 3 months.

That's a bit complicated.

Here's a SQL Fiddle as a demonstration: http://sqlfiddle.com/#!2/c90e9/3.

For testing, NOW() is inconvenient because it always returns today's date. So, for testing, we replace all occurrences of NOW() with a user-defined variable @now, and set that to various dates, so we can appropriately test.

Here's the SQL statement I used for testing. The first expression is the conditional test we're planning on using in the WHERE clause. For testing, we want to return all the rows, and just see which rows get due_in_3mo flagged as TRUE (1) and which get flagged as FALSE (0).

The second expression in the SELECT list just the "next due" deadline date, same as used in the first expression.

The rest of the expressions are pretty self-explanatory... we also want to display the date 3 months in the future we're comparing to, and the original "deadline" date value.

SELECT 
       CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d')) 
       + INTERVAL CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d'))<@now YEAR 
       < @now + INTERVAL 3 MONTH   
    AS `due_in_3mo`

     , CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d')) 
       + INTERVAL CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d'))<@now YEAR 
    AS `next_due`

     , d.id
     , d.deadline + INTERVAL 0 DAY AS `deadline`
     , @now + INTERVAL 3 MONTH AS `now+3mo`
     , @now + INTERVAL 0 DAY AS `now`
  FROM d d
 CROSS
  JOIN (SELECT @now := '2015-11-01') i
 ORDER BY d.id

Change the value assigned to @now in the inline view (aliased as i) to test with other date values.

(You may want to use DATE(NOW()) in place of NOW() so that times don't get mixed in, and you may want to subtract another day from that, that really just depends how you want to handle the edge case of a deadline with month and day the same as the current date. (i.e. do you want to handle that as "in the past" or not.)

To summarize the approach: generate the "next due" deadline date as a DATE value in the future, and compare to the a date 3 months from now.

Upvotes: 2

Related Questions