INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Database

INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

I am trying to have a where clause that checks if the current_date is less than a date field minus 14 days.  This is a sample of the code.

SELECT PROD_ID, LOC_ID FROM PRODUCT WHERE CURRENT_DATE < (PROMO_DATE - INTERVAL '14' DAY)

When I run this I get that "Invalid date supplied for PROMO_DATE".  I have tried casting it as a date and that doesn't work either.  

Any suggestions would be greatly appreciated.

10 REPLIES

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Additional information - the PROMO_DATE field is defined as such:

PROMO_DATE DATE FORMAT 'YY/MM/DD' NOT NULL DEFAULT DATE '0001-01-01'

I've got a feeling that is where my problem lies, but I'd love to hear from the community and see if there is a workaround.

N/A

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

WHERE CURRENT_DATE < PROMO_DATE - 14

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Ulrich - that gets a "Invalid date supplied" error as well.  I think it may be the way the field is defined in the table.

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Your table could have an invalid date for one or more rows in the column PROMO_DATE. The query you have mentioned in your first post should work just fine.

Can you please validate the values present in PROMO_DATE to make sure they have all valid dates?

Thanks.

PT

N/A

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

This seems like a problem of your source data...


select count(*)

from sys_calendar.calendar

WHERE CURRENT_DATE < calendar_date - 14


works.


Do you really select from a table?


try 


show select ...;


This will show you which DB objects are involved.


Also 


PROMO_DATE DATE FORMAT 'YY/MM/DD' NOT NULL DEFAULT DATE '0001-01-01'


The format and the default data are using different formats. Will this work?

N/A

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Your default date is '0001-01-01', which is the minimum date in Teradata, you can't get any lower date, but your query tries to substract 14 days.

Rewrite it as WHERE CURRENT_DATE + 14 < PROMO_DATE, which is the preferred way as it allows index/partition access.

Btw, in TD13 the optimizer should automatically rewrite your original condition.

Dieter

N/A

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

this is a nice one dieter ;-)

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Have to agree with that :-)

-PT

Re: INTERVAL SUBTRACTION FROM DATE FIELD IN WHERE CLAUSE

Hi ,

Would like to repost on this, suppose i want to use a column from a sub query in the place of the days in the Interval day--

Something like --

 SELECT (CAST(a.rptg_dt AS DATE) (FORMAT 'YYYY-MM-DD')) -INTERVAL '1' DAY||'%'||trim(((a.rptg_dt (FORMAT 'YYYY-MM-DD')) - INTERVAL '1' DAY) - b.rptg_dt)||'%'||(b.rptg_dt (FORMAT 'YYYY-MM-DD'))||'%'||TRIM(((CAST(a.rptg_dt AS DATE) - INTERVAL '1' DAY) + INTERVAL ''b.aging_days_cnt'' DAY) (FORMAT 'YYYY-MM-DD'))||'%' (TITLE '')

 FROM   dd_app.table1 a , (SELECT seo_cd,rptg_dt,coalesce(aging_days_cnt,0) aging_days_cnt FROM db_app.table2 WHERE report_id =101 GROUP BY geo_cd,rptg_dt,aging_days_cnt) b

 WHERE  a.geo_cd = b.geo_cd;

is this possible??

thanks,

bg