DATE_ADD 2

[hansi58]

Hi

I have to increment a date (cli_fact_start in date format) adding all the time 1 month....

OK I do that with
UPDATE TClient SET cli_fact_start = TIMESTAMPADD(MONTH, 1, cli_fact_start)

When the date is 2004-01-31 it gives
2004-02-29

Super... exactly what I wanted... However the next one it gives
2004-03-29 instead of 2004-03-31

Does anybody have an Idea how I can get all the time the end of the month.

Thank you very much

Hans, Montréal

 

[vanekl]

UPDATE TClient SET cli_fact_start =
IF(month( cli_fact_start + interval 1 day ) <> month(cli_fact_start),
(cli_fact_start + interval 1 day) + interval 1 month - interval 1 day,
cli_fact_start + interval 1 month)

 

[KarveR]

DATE_ADD(datefield, interval 1 month)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[tshot]

as star for vanekl from me also

for last day, 4.1.1 has introduced a functionl ast_day()

LAST_DAY(date) 
Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

----------------
ur feedback is a very welcome desire