Search code examples
teradatateradata-sql-assistant

Add "Months" to YYYYMM Integer

I have an integer column that stores a yyyymm "date." How can months be added to it?

CREATE VOLATILE TABLE tbl
(
prcs_mn int
) ON COMMIT PRESERVE ROWS;

INSERT INTO tbl VALUES (201602);
INSERT INTO tbl VALUES (201612);

I would like to add two "months" and return:

201604
201702

I'm assuming cast is needed in some way; however, the syntax is befuddling me.

The first solution below gives a date 'mm/dd/yyyy' format. I added a second cast which resulted in a 'yyyy-mm' format with the hyphen. Am I missing something where that hyphen can be removed? Otherwise I wrapped the whole thing in the oreplace function. This seems to work but is rather unsightly...

select prcs_mn
     , add_months( cast( cast(prcs_mn as varchar(6)) as date format 'yyyymm'), 2) as "mm/dd/yyyy"
     , cast(add_months(cast(cast(prcs_mn as varchar(6)) as date format 'yyyymm'), 2) as varchar(7)) as "yyyy-mm"
     , oreplace(cast(add_months(cast(cast(prcs_mn as varchar(6)) as date format 'yyyymm'), 2) as varchar(7)), '-', '') as yyyymm
from tbl;

prcs_mn mm/dd/yyyy  yyyy-mm yyyymm
201612  02/01/2017  2017-02 201702
201602  04/01/2016  2016-04 201604
Solution

  • For a char column:

    To_Char(Add_Months(To_Date(prcs_mn, 'YYYYMM'),2), 'YYYYMM')

    For an int column it's more efficient to avoid the double cast from/to string and apply some logic based on internal storage:

    (Add_Months(Cast((prcs_mn - 190000) * 100 + 1 AS DATE), 2) / 100) + 190000

    You should talk to your DBA to create a SQL UDF for this calculation.