Add days to a date column

Database
Highlighted

Add days to a date column

I need to add 90 days to a Date column (Ex: Dealer_Date) in a table (Ex:Dealer)
Data in date column is in format :mm/dd/yyyy

When I say :
Select
Current_Date+90 .
It works fine and gives me the result.

But when I say:
Select
Dealer.Dealer_Date+90 from
Dealer
It says "invalid date supplied for Dealer_Date".

Can you please help me out.
6 REPLIES
Enthusiast

Re: Add days to a date column

I tried as below and I got the result.

CREATE SET TABLE db1.emp2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(emp_id INTEGER NOT NULL,
release_date DATE format 'mm/dd/yyyy')
UNIQUE PRIMARY INDEX ( emp_id );

insert into db1.emp2 values (1,current_date);

sel * from db1.emp2;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

emp_id release_date
----------- ------------
1 06/11/2009

sel release_date + interval '90' DAY from db1.emp2;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(release_date+ 90)
------------------
2009-09-09

Thanks

Re: Add days to a date column

I tried

select Dealer.Dealer_Date+ interval '90' DAY from Dealer

I also tried

select cast(Dealer.Dealer_Date as date format 'mm/dd/yyyy')+ interval '90' DAY
from Dealer

Both ways it says "invalid date supplied for Dealer_Date".

Can you help me out.
Enthusiast

Re: Add days to a date column

What is the MAX(Dealer_Date) on the table and does the field allow NULL values?

Re: Add days to a date column

MAx of the date is :12/31/9999

Dealer_Date column is defined as:
Dealer_Date FORMAT 'yy/mm/dd' NOT NULL
Enthusiast

Re: Add days to a date column

12/31/9999 is the upper limit of the DATE domain on Teradata. You can not add any days to it. This is why your query is failing.

Re: Add days to a date column

Thanks :)