0

Simple question, but I haven't found any examples of this on Stack Exchange:

I need to be able to select all records in the current billing cycle, that's it. The catch is the billing cycle doesn't begin on the first of the month, so I can't just do MONTH(date) = MONTH(CURRENT_DATE()). I can compare the parts of the date individually, but handling wraparound for months and years is a pain. For example, if the billing cycle starts on the 15th of each month, then as of the date this is being posted, the date would be 2023-11-15, not 2023-12-15. And a month from now, it would be 2023-12-15, a different month and year from the current date.

Because the SQL gets long and messy just handling this filter, I'd like to instead generate the start of the last billing date once and then compare the entire date against that directly, but I'm having some trouble putting that together. e.g. WHERE date >= '2023-11-15' (but generating this date dynamically). Subtracting INTERVAL 1 MONTH to get the month in which the billing cycle starts doesn't work, since that's only the case if it's before the day of the billing cycle in the current month. Is there an elegant way to compute the most recent nth day of the month in MySQL/MariaDB, whether that day is in the current month or the previous one?

1 Answer 1

1

I would suggest sticking this into a UDF to avoid cluttering up your SQL statement (especially if you want/need to parameterize which day of the month you're looking for) but it seems like the key bit is where you say "that's only the case if it's before the day ..." which led me directly to this:

select
    case when dayofmonth(current_date) <= 15 
        then date_add(
                date_sub(current_date, interval 1 month), 
                interval 15 - dayofmonth(current_date) day)                     
        else date_add(current_date, interval 15 - dayofmonth(current_date) day)
    end as LastFifteenth
3
  • I had to change DAYS to DAY to run this, but it seems to spit out 2023-12-15 for me, not 2023-11-15 - is that what you get? Dec 12, 2023 at 23:56
  • With the caveat that I wrote it in DB2 SQL and then tried to translate to MYSQL for you, what I wrote produces 2023-11-15 for me.
    – Hellion
    Dec 13, 2023 at 14:56
  • Aha! It should be DAYOFMONTH not DAY. The basic logic looked right but I knew something was weird. Dec 13, 2023 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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