TLDR: I want to get a table with the previous two months in Teradata, based on CURRENT_DATE. Currently I can only get the previous month: Expected output is: Long version: I want something, that could be used in a bigger query like this, valid for every day of the year, without hardcoding the dates. The bigger query with the hardcoded

Teradata SQL returning previous two months, based on CURRENT_DATE

TLDR: I want to get a table with the previous two months in Teradata, based on CURRENT_DATE. Currently I can only get the previous month:

SELECT 
TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -1), 'YYYY-MM') MM;

Expected output is:

+--------+
|   MM   |
+--------+
| 2020-01|
| 2019-12|
+--------+

Long version: I want something, that could be used in a bigger query like this, valid for every day of the year, without hardcoding the dates. The bigger query with the hardcoded dates looks like this:

AND TO_CHAR(SOME_DATE, 'YYYY-MM') IN ('2020-01', '2019-12')

and it works perfectly. The below one returns results ok, but only for 1 month.

AND TO_CHAR(SOME_DATE, 'YYYY-MM') IN 
(
TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -1), 'YYYY-MM')
)

Tried to add a comma and add the same line with -2, did not work:

AND TO_CHAR(SOME_DATE, 'YYYY-MM') IN 
(
TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -1), 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -2), 'YYYY-MM')
)

the error is:

SELECT Failed 3706: Syntax error: expected something between ‘)’ and ‘.’.

Answer

Why not use an or

select * from table where 
   (SOME_DATE =  To_char(ADD_MONTHS(CURRENT_DATE , -2),'YYYY-MM') OR 
   SOME_DATE =  To_char(ADD_MONTHS(CURRENT_DATE , -1),'YYYY-MM'))

Advertisement

Answer