How to find in MySQL the nearest (time wise) entry to a given date when only given year or only year and month?

Question

This is very similar to a question that has already been answered (that I can't re-find right now) but the answers only let you get the nearest entry when you have a full date (year, month and day).

Answer 1

I'd go with DATEDIFF and construct a date string assuming '01' as the day / month. Not nice, but should work though.

Answer 2

A simple ABS(DATEDIFF()) might be exactly what's needed. But, for example, given the reference date of '2009', is 2009/01/10 or 2008/12/31 the correct date to return? Depending on your requirement you might use the middle of the period, or end, rather than the start.

Another consideration is what to do if more than one date is equally distant from the reference. Given '2009' as the reference date, which is closest: 2009/06/30 or 2009/07/01? One interpretation might be that both are the same 'distance' from 2009 - zero years. Do you need to have some rule for picking just one date (could be a simple as just taking the first date) or do you want all 'equally distant' dates reported.