Archives of the TeradataForum

Message Posted: Wed, 31 Dec 2003 @ 17:38:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Not exactly Bill- Vinod has a point that the last day(s) of the month are treated slightly differently via Add_Months. But what might be confusing to some is that it only works in one direction - If adding/subtracting a month returns an invalid date (say 11/31), then Teradata returns the actual last day of the month (11/30)...

So ADD_MONTHS(DATE '2003-10-31', -1)
returns 2003-09-30, since 2003-09-31 is invalid.

So ADD_MONTHS(DATE '2003-09-30', -1)
returns 2003-08-30 (not 2003-08-31) since 2003-08-30 IS valid.

So you would not want to use Add_Months if you always want to return the last day-

The method Dieter's suggested is better:
DATE '2003-11-30' - EXTRACT(DAY FROM DATE '2003-11-30')

	<Prev	Next>		<<First	<Prev	Next>	Last>>