teradata pass through, getting wrong date range

[Steelers_In_DC]

When I use the below code I get a date range from 31JAN2011 to 30JUN2017.  When I pull this data in teradata studio the date is in the format of 'yyyy-mm-dd'.

I have tried using datepart, or using the date like this date'2016-12-31'.  I have also tried doing '2015-12-31' <= load_date <= '2016-12-31'.  Nothing seems to be working for me.

Any ideas are appreciated.

proc sql ;
connect to TERADATA(SERVER=TPRDDB user=&USER  password= "&PW" mode=teradata SESSIONS=200);
create table FDR_NCC_MASTER_RECORD as select * from connection to TERADATA
(
select
LOAD_DATE,
origacct,
credline,
extstat_pm
from EDWTD01P_ETL_STAGE_V.FDR_NCC_CONSUMER
where LOAD_DATE BETWEEN '2015-12-31' and '2016-12-31' and
      extstat_pm not in('B','Z','C') or
      extstat_pm = ''
order by origacct,load_date
;
);
disconnect from TERADATA;
quit;

[RW9]

I think your should use date literals, not strings:

where LOAD_DATE BETWEEN '31JAN2011'd and '30JUN2017'd and...

Note the d after the dates.

[Steelers_In_DC]

When I run that i get the following error:

Teradata prepare: Syntax error, expected something like an 'AND' keyword between a string or a Unicode character literal and

the word 'd'.

[Tom · Accepted solution]

I think the error is probably in the other parts of your WHERE condition.

create table FDR_NCC_MASTER_RECORD as 
  select * from connection to TERADATA
(
select LOAD_DATE
     , origacct
     , credline
     , extstat_pm
from EDWTD01P_ETL_STAGE_V.FDR_NCC_CONSUMER
where LOAD_DATE BETWEEN '2015-12-31' and '2016-12-31'
  and extstat_pm not in('B','Z','C') 
  or  extstat_pm = ''
order by origacct , load_date
)
;

What did you mean by the last condition?  Are you intendting that to match NULL values in Teradata?  And if it did (which I doubt it will) then that means you all taking all observations with missing EXSTAT_PM not matter what value of LOAD_DATE they have.

Also you could explicitly let Teradata know that the you meant date literals, but ti is probably not required.

where LOAD_DATE BETWEEN DATE'2015-12-31' and DATE'2016-12-31'
  and (extstat_pm not in('B','Z','C') or extstat_pm is null)