List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 8 1999 4:23am
Subject:Re: Can I rely on date_add to land on a valid date.
View as plain text  
On Fri, 1999-10-08 10:12:36 +0800, John Hennessy wrote:
> The Plan:-
> 
> 1) The "Start" date is the 29th, 30th or 31st.
> 2) I DATE_ADD 1 month and the date truncates back to the allowable
>    date.  (Great!).
> 3) The next month I want the "Due" date to return to the original "Start"
>    day (where possible) but in the new "Due" month.

Okay, I understand, what you want now ...

(which BTW is different from what your first posting said: there your
table schema showed, that you'd like to add periods of months to a
start date ...)

You really could've saved us all time, if you'd made things clearer in
the first place ...

> As you can see all I really need to do is restore the original day
> in the months that allow it and have MySQL pull it back to a valid
> date in months that don't.

Let this sentence of yours be the algorithm!

"retore the original day ...":
  CONCAT( DATE_FORMAT('%Y-%m-', thedate), DAYOFMONTH(start) )

"have MySQL pull it back to a valid date":
  DATE_ADD( ..., INTERVAL 0 MONTH)

Together:
  DATE_ADD( CONCAT( DATE_FORMAT('%Y-%m-', thedate)
                  , DAYOFMONTH(start) )
          , INTERVAL 0 MONTH)

As it's not very clean to have a possibly invalid date as part of the
calculation, a conceptual better solution is to start in January
(which has all the 31 possible days of month):
  DATE_ADD( CONCAT( DATE_FORMAT('%Y-01-', thedate)
                  , DAYOFMONTH(start) )
          , INTERVAL MONTH(thedate)-1 MONTH)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
Can I rely on date_add to land on a valid date.John Hennessy7 Oct
  • Re: Can I rely on date_add to land on a valid date.Thimble Smith7 Oct
  • Re: Can I rely on date_add to land on a valid date.John Hennessy8 Oct
    • Re: Can I rely on date_add to land on a valid date.Thimble Smith8 Oct
    • Re: Can I rely on date_add to land on a valid date.Martin Ramsch8 Oct
      • Question about Random OrderFraser MacKenzie8 Oct
        • Re: Question about Random OrderMartin Ramsch8 Oct