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:
x
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 ‘.’.
Advertisement
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'))