All Forums Database
dmaclagan 7 posts Joined 01/12
16 Feb 2012
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.

dmaclagan 7 posts Joined 01/12
16 Feb 2012

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.

ulrich 816 posts Joined 09/09
16 Feb 2012

WHERE CURRENT_DATE < PROMO_DATE - 14

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dmaclagan 7 posts Joined 01/12
16 Feb 2012

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

addsmiles 13 posts Joined 01/12
16 Feb 2012

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

ulrich 816 posts Joined 09/09
17 Feb 2012

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?

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
17 Feb 2012

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

Dieter

ulrich 816 posts Joined 09/09
17 Feb 2012

this is a nice one dieter ;-)

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

addsmiles 13 posts Joined 01/12
17 Feb 2012

Have to agree with that :-)

-PT

05 May 2014

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

Fred 1096 posts Joined 08/04
05 May 2014

The interval itself must have a constant within the quotes, but you can multiply by an integer value:
b.aging_days_cnt * INTERVAL '1' DAY.

You must sign in to leave a comment.