I am trying to get the data for the last 6 months.
This is what I have used:
WHERE d_date > DATEADD(m, -6, current_timestamp)
and I am getting this error.
ERROR: CLI prepare error: SQL0206N "M" is not valid in the context where it is used
also tried
WHERE d_date > current date -180
and got this error:
ERROR: CLI prepare error: SQL0171N The data type, length or value of the argument for the parameter in
position "2" of routine "-" is incorrect. Parameter name: "". SQLSTATE=42815
Please advice.
Based on Andriy's eagle-eyes, here is (I think) the DB2 syntax:
WHERE d_date > current_date - 6 MONTHS
And here is a link to a pretty good function to mirror DATEADD in DB2.
Also, since you mentioned SAS, here is the SAS syntax to do the same thing:
WHERE d_date > intnx('MONTH', today(), -6, 'SAME');
Although you say you are running this with SAS Enterprise Guide, the syntax you show is not SAS. The error message you are getting suggests you are submitting "pass-thru" code directly to a database.