Teradata SQL returning previous two months, based on CURRENT_DATE

Question

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 '.'.

Based on the deleted answer/comments: is it a simple union you want, two rows with 2020-01 and 2019-02 as values? — Turo, Feb 19 '20 at 09:15
@Turo - Actually, I want something, that could be used in a bigger query like this: `AND SOME_DATE IN ('2020-01', '2019-12')`, without hardcoding the dates. — Vityata, Feb 19 '20 at 09:16
@Turo - I guess nothing is wrong with both of them, if they can provide the needed answer, without hardcoding. — Vityata, Feb 19 '20 at 09:26
Teradata doesn't allow calculated expressions in `IN` (for a single condition it's probably converted to `=`) , don't know why this stupid restriction exists. — dnoeth, Feb 19 '20 at 10:59
@dnoeth - this is something I also thought of, but was not sure. Anyway, they really should have a good security reason behind it. — Vityata, Feb 19 '20 at 11:15

dnoeth · Answer 1 · 2020-02-19T12:34:33.110

Your calculation is too compliated:

TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -1), 'YYYY-MM')
= To_Char(Add_Months(Current_Date, -1), 'YYYY-MM')

TO_CHAR(ADD_MONTHS(CURRENT_DATE  - EXTRACT(DAY FROM CURRENT_DATE )+1, -2), 'YYYY-MM')
= To_Char(Add_Months(Current_Date, -2), 'YYYY-MM')

But your approach to compare YYYY_MM is totally wrong.

Assumimg SOME_DATE is actually a DATE CASTing it to a string results in loosing statistics and a Full Table Scan if the table is partition by that date. Both can possibly lead to a bad plan.

You should keep the date column as-is and do all calculation on CURRENT_DATE:

WHERE som_date BETWEEN Trunc(Add_Months(Current_Date, -2), 'mon')
                   AND Current_Date - Extract(DAY From Current_Date)

`AND Last_Day(Current_Date)` would give the current month, I want "previous" and "previous previous". But the idea is a good one. — Vityata, Feb 19 '20 at 11:18

Turo · Accepted Answer · 2020-02-19T09:39:29.790

1

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'))

edited Feb 19 '20 at 09:39

answered Feb 19 '20 at 09:36

Turo

4,724
2
14
27

`SOME_DATE` is a char, thus it should match these two. – Vityata Feb 19 '20 at 09:37
then its an or. – Turo Feb 19 '20 at 09:38
Hm... This would probably work, but `SOME_DATE` is calculated twice and would slow it down. Take a look at the updated question, I have added more info. – Vityata Feb 19 '20 at 09:42
so there is no valid value in between and the previuos answer would have worked. But what you reale want ist to check if the date is i.e. between 2019-12-01(first of preprevious month) and 2020-01-31(last of previous month), rigtht? – Turo Feb 19 '20 at 09:50
Well, currently the query runs perfectly like this: `AND TO_CHAR(SOME_DATE, 'YYYY-MM') IN ('2020-01', '2019-12')` and the only thing I want is to replace the `('2020-01', '2019-12')` with calculated values and not hardcoded ones. I do not want to change the structure of the query. – Vityata Feb 19 '20 at 09:53
It's the wrong way, since your formatting the date for every row without needing it, but here you find the workaround for the missing tables in the union. https://stackoverflow.com/questions/49901803/what-is-teradatas-equivalent-for-oracles-dual – Turo Feb 19 '20 at 10:04
Actually if I have to replace the `1` in the query of the link with the whole `TO_CHAR(ADD_MONTHS(...`, I better go with the `OR` from your answer. – Vityata Feb 19 '20 at 10:14

score 0 · Answer 3 · answered Feb 19 '20 at 09:28

0

Try something with sub query:

select * from table where date in 
(SELECT To_char(ADD_MONTHS(CURRENT_DATE , -2),'YYYY-MM') MM
union 
SELECT To_char(ADD_MONTHS(CURRENT_DATE , -1),'YYYY-MM') MM
);

answered Feb 19 '20 at 09:28

Pankaj_Dwivedi

565
4
15

I have changed the `IN` part of my big query, but it gave me an error, `3888 - A SELECT for UNION, INTERSECT or MINUS must reference a table.` – Vityata Feb 19 '20 at 09:32
RIght, for these cases ther is SYSDUMMY in DB2, dual in Orcale, but Teradata i don't know – Turo Feb 19 '20 at 09:34
for teradta there is no dummy table. Simply select 1; will give you 1. Select 1 where 1= 1; will also give you result. – Pankaj_Dwivedi Feb 19 '20 at 09:36

Teradata SQL returning previous two months, based on CURRENT_DATE

3 Answers3