Adding month to datetime20.0 value

[cynthya]

Hi,

I have a table with date values. The dates in datetime20.0 format.

I would like to add 22 months to those value using an sql procedure.

What is the easiest way to do this?

Thank you for your help and time.

[jakarman]

SAS(R) 9.3 Functions and CALL Routines: Reference (intnx and the related fucntions)
Be aware as this is a SAS function Performance will be the best wiht that for SAS-datasets.

When you are dealing with an external DBMS this can become challenging as date/time values are not standardized.

[Tom · Accepted solution]

Use the INTNX() function.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

If you are using DATETIME20.0 format then you have DATETIME values instead of DATE values.  So use the DTMONTH interval instead of the MONTH interval.

data _null_;

  now=datetime();

  future = intnx('dtmonth',now,22-1,'S');

  format now future datetime22.;

  put (_all_) (=/);

run;

now=07NOV2013:15:23:08

future=07AUG2015:15:23:08

You can use the function call in SQL the same as you would in a DATA step, but you might need to give the result a variable name.

select mydate

     , intnx('dtmonth',MYDATE,22-1,'S') as future_date format=datetime22.

from mytable

[cynthya]

Thank you very much Tom.

This works perfectly.