Archives of the TeradataForum

Message Posted: Mon, 24 Sep 2012 @ 20:04:20 GMT

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

You don't need a user-defined function to add or subtract months from an integer YearMonth value, provided the months are stored with a leading zero when the month number is less than 10, that is, for example: 201209

Why not take the numeric YYYYMM string, cast it as a date (maybe suffixing the digits with a trivial '01' for the day digits), do the ADD_MONTHS function on the result and recast the resulting date as a string from which the YYYYMM digits can be extracted and recast as an integer value?

If one had a YYYYMM integer of 201208 stored in an attribute called "YearMo" (in database "DbName") one could do something like the following to get a value for seven months downstream:

     CAST(SUBSTRING( (ADD_MONTHS (CAST( (CAST(DbName.YearMo AS CHAR(6)) || '01')
     AS DATE FORMAT 'YYYYMMDD'), 7) (DATE, FORMAT 'YYYYMMDD')) FROM 1 FOR 6) AS
     INTEGER)

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