getting an error on this code

UDA
Enthusiast

getting an error on this code

Im getting an error expecting something between year and i dont understand whats causing it.

SELECT *
FROM edw_access_views.calendar cal
WHERE cal.calendar_date = YEAR(CURRENT_DATE)||'-'||'01'||'-'||'01'
)
AND (
SELECT MAX(calendar_date)
FROM edw_access_views.calendar cal
WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)||'-'||'01'||'-'||'01' AND CURRENT_DATE)
)
OR cal.calendar_date BETWEEN (
SELECT MIN(calendar_date)
FROM edw_access_views.calendar cal
WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)-1||'-'||'01'||'-'||'01' AND CURRENT_DATE-365)
)
AND (
SELECT MIN(calendar_date)
FROM edw_access_views.calendar cal
WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)-1||'-'||'01'||'-'||'01' AND CURRENT_DATE-365)
)
4 REPLIES
Teradata Employee

Re: getting an error on this code

YEAR is not a Teradata function.
Use EXTRACT(YEAR FROM CURRENT_DATE).
Enthusiast

Re: getting an error on this code

I dont understand if I run Year(current_date) i return 2009.
Enthusiast

Re: getting an error on this code

WHERE (cal.calendar_date BETWEEN SELECT ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE) + 1,-MONTH(CURRENT_DATE)+1) AND CURRENT_DATE OR cal.calendar_date BETWEEN SELECT ADD_MONTHS((CURRENT_DATE-365)-EXTRACT(DAY FROM (CURRENT_DATE-365)) + 1,-MONTH((CURRENT_DATE-365))+1) AND CURRENT_DATE-365)

I tried this as well and I get the expecting something error.
Teradata Employee

Re: getting an error on this code

You are connecting via ODBC with "parsing" (also called Use of ODBC Extensions) enabled.

YEAR, MONTH, etc. are ODBC functions, and in simple cases the driver can fix the SQL to be Teradata compatible and/or fix the answer once it is returned to the client. But when used as part of a more complex expression, it can get confused.