Thursday, February 17, 2011

Issue in INTERVAL function

I kept getting an annoying error, while I was trying to use interval function on timestamp field. This ended up to be an bug in teradata's feature. Totally surprised they have not handled this issue. Hopefully they will fix it after my DBA raises a ticket.
Here is the issue:
INTERVAL function does not adjust dates(for leap year or for february) as ADD_MONTHS does. For example, when you execute the following:
SELECT ADD_MONTHS('2011-01-31',1)
You will get an output as 2011-02-28
But, when you use interval function instead:
SELECT CAST('2011-01-31' AS DATE)+ INTERVAL '1' MONTH
You will get an error (2665: Invalid date).

Weird! isn't it? hopefully they fix it sooner and my post will be obsolete.

4 comments:

  1. Teradata manual itself says they dont work directly, so I think they are aware but not sure when they might fix or they have plans of fixing.

    SQL functions: page 152

    For example, the following operation returns an error because it evaluates to a date that is not
    valid (‘1996-09-31’).
    SELECT DATE '1996-08-31' + INTERVAL '1' MONTH;
    The desired result is obtained with a slight rephrasing of the second operand.
    SELECT DATE '1996-08-31' + INTERVAL '30' DAY;
    This operation returns the desired result, ‘1996-09-30’. No error is returned.

    ReplyDelete
  2. It is not a teradata bug. According to the teradata manual, that is what it is. That is the reason why they have ADD_MONTHS function :-)

    ReplyDelete
  3. it is not a feature. so, was wondering why can't they fix it? does it have any meaningful justification apart from being 'documented'? if add_months is an alternative, why not remove this functionality(interval 'n' month) completely?

    ReplyDelete
  4. HI mam i got a doubt ..when we are extracting and tranform the data to staging area which one is best ..fastload or bteq...for transform and extract which one is best please tell me

    ReplyDelete