get last three month data

Database
N/A

get last three month data

Hello,

 

I am new to Teradata. I am having column month1 in YYYYMM format (201601,201602,201603...)and want last three month data. Its work fine with >max(month1)-4, but it  is failing for jan month (201601)

Please let me know any different way.

 

 

 

 


Accepted Solutions
rjg
N/A

Re: get last three month data

If you want to do date arithmetic you will need to cast to date.

 

SyntaxEditor Code Snippet

sel add_months(Cast(substr('201601',1,4)||'-'||substr('201601',5,2)||'-01' as date) ,-3);

Rglass 

1 ACCEPTED SOLUTION
5 REPLIES
rjg
N/A

Re: get last three month data

You can use add_months function.

 

Sel add_months(month1, -3)

N/A

Re: get last three month data

add_months work for only date type columns. here it is yyyymm asnd char

rjg
N/A

Re: get last three month data

If you want to do date arithmetic you will need to cast to date.

 

SyntaxEditor Code Snippet

sel add_months(Cast(substr('201601',1,4)||'-'||substr('201601',5,2)||'-01' as date) ,-3);

Rglass 

N/A

Re: get last three month data

You need to cast to a date and then back to a string:

Trim(Add_Months(Cast(col AS DATE Format 'YYYYMM'),-4) (Format 'YYYYMM'))
N/A

Re: get last three month data

Correct and logical.