I just thought i should mention something regarding this question. To me its probably a sign of bad design when a system requires logic to determine end of month dates (unless its a date module in of itself...)

IME the most common reason to require an end of month date is when you have dated assignments, such as "this customer pays that rate from this date to that date". A naive DB implementation would have the start date and end date be inclusive, so an assignment for january would be Jan 1 - Jan 31. This is a problematic design for many reasons. It requires annoying code to determine the end of the month, and it makes writing queries much more difficult by introducing a bunch of weird edge cases. For instance if the From date is Jan 01 00:00:00 and the To date is Jan 31 00:00:00 what happens for the 24 hours following Jan 31? So now you have to start doing things like Jan 31 23:59:59, which is just a pain. Range queries likewise become really annoying. (To make matters even worse, its not unusual to use NULL as an end date to imply an "open assignment". This just makes queries more complex and slower.)

A much better design IMO is to make date ranges inclusive on the from date and exclusive on the to date, in other words an interval [From,To) and require that end dates are defined (use a far future date to represent open assignments). This makes the logic much simpler. You avoid dealing with end of month dates as you can say Jan01-Feb01, you avoid dealing with time gap problems, and can reduce quite complex queries down to very simple logic. For instance to find all assignments for a particular customer for a particular time period you can say:

  select * from assignment
  where customer=$customer
  and start_date   < $end_period
  and $start_period < end_date
[download]