Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

"RS0065: Invalid date value" when adding month/year interval

by markc » Fri Aug 26, 2016 1:52 pm

I keep getting an RS0065 error when adding a 1 month interval to a date. Why is this, and how can I work around it?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: "RS0065: Invalid date value" when adding month/year inte

by markc » Fri Aug 26, 2016 1:57 pm

A typical cause of this is trying to add 1 month to e.g. 31st March, which generates 31st April. This is an invalid date. If we do this with a literal query you can see the problem (it generates CG0065 rather than RS0065 here, but the underlying problem is the same):

>select date '2016-03-31' + interval '1' month;
Query 2 retcode = -1 ---- 0:00.0 0:00.0 0:00.0
22008: [Kognitio][WX2 Driver][localhost] CG0065: Invalid date value
>

This is dictated by the SQL standard.

However, you can use the compatibility function, add_months, to get the same behaviour as other database products display here:

>select add_months('2016-03-31',1);
ADD_MONTHS
2016-04-30
Query 3 1 row ---- 0:00.0 0:00.0 0:00.0
>

Note that you can get the same behaviour by adding a year to 29th February in a leap year, and to resolve that you can use add_months again:

>select date '2016-02-29' + interval '1' year;
Query 5 retcode = -1 ---- 0:00.0 0:00.0 0:00.0
22008: [Kognitio][WX2 Driver][localhost] CG0065: Invalid date value

>select add_months('2016-02-29',12);
ADD_MONTHS
2017-02-28
Query 7 1 row ---- 0:00.0 0:00.0 0:00.0
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron