Question:
Both of the following questions deal with the extend function; more specifically, how Informix handles dates:

1. I’ve used “extend(var, day to day)” in a program since December ’96 with no problems.

But this week, it’s been crashing, with this message:
1263: A field in a datetime or interval value is out of range or incorrect.

If the date in the file is “1997-01-31,” “extend(var, day to day)” doesn’t work. If the date is “1997-02-01,” it works.

Does this have something to do with the current month?

Please help!

2. We have a problem when we use the EXTEND statement when the first parm is DAY (EXTEND (field), DAY to DAY).

The problem occurs because we are in February and the dates returned include days between 29 and 31. But when we try to retrieve data from January, we canextract only the day. The message we are getting is:

ERROR NBR : -1263 A field in a datetime or interval value is out of range or incorrect.
ISAM ERROR: Unknown error message 0.

Answer:
The problem here is how Informix has decided to handle invalid(or potentially invalid) dates. For instance, look at an example using date math:

date (“1/31/97”) + 1 units month

What date should be the result of this expression?

February 28, 1997       Match end of month        March 1, 1997           Add 29 days        March 2, 1997           Add 30 days        March 3, 1997           Add 31 days

Because different applications may expect different results, the result of this expression in Informix is undefined and produces an error.

If you are working in 4gl, you can write functions you can call to avoid these types of errors. An example of such a function might be:

{*********************************************************************add_months_to_date
Function to add months to a date. For end-of-month out-of-range cases, result should be the last day of the result month.
Input: p_date date to add to
p_months number of months to add
Output: p_result p_date + p_months
**********************************************************************}

function add_months_to_date (p_date, p_months)  define p_date         date,           p_months             smallint,           p_result             date,           p_temp_date  date,           p_new_month  smallint,           p_new_year     smallint  if day (p_date) >= 30 then    { Handle any potentially out of range dates }    let p_temp_date = mdy (month(p_date), 1, year(p_date))    let p_temp_date = p_temp_date + p_months units month    let p_new_month = month (p_temp_date)    case p_new_month        when 1, 3, 5, 7, 8, 10, 12  { Months with 31 days }                let p_result = mdy (p_new_month, day(p_temp_date),                                          year(p_temp_date))         when 4, 6, 9, 11  { Months with 30 days }                let p_result = mdy (p_new_month, 30, year(p_temp_date)      when 2  { February }                { Leap year rules: every four years                   except for centuries evenly divisible by 400 }                let p_new_year = year (p_temp_date)                if (p_new_year mod 4 = 0) and               (p_new_year mod 100 <> 0 or                    p_new_year mod 400 = 0) then                  let p_new_day = 28                else                  let p_new_day = 29                end if                let p_result = mdy (p_new_month, p_new_day,                                          year(p_temp_date))    end case  {* p_new_month *}  else    let p_result = p_date + p_months units month  end if  return p_resultend function {* add_months_to_date *}

If you want to, you can create a stored procedure to do this, andthen you have a sql solution to this problem. This, however, is left as an exercise for the reader.

An elegant solution I have always liked for calculating the last day of the month is to let the database engine do the work for you. To do this, take advantage of the fact that the first day of the month minus one day is the last day of the previous month. By doing this you avoid having to do much thinking at all, as demonstrated in the following 4gl code:
{********************************************************************** last_day_of_month
Function to calculate the last day of a given month.
Input: p_date date to calculate last day of month for
Output: result_date date of last day of the month of p_date
**********************************************************************}

function Last_Day_of_Month (p_date)  define p_date       date  define temp_date    date  define result_date  date  let temp_date = (month(p_date), 1, year(p_date))  let result_date = (temp_date + 1 units month) – 1 units day   return result_dateend function  {* Last_Day_of_Month *}

The nice thing about this second function is that it is going tobe pretty easy to translate into a SQL-only solution:

select ((mdy(month(mydate), 1, year(mydate)) + 1 units month)       – 1 units day) last_date_of_month  from mytable  where …

As you can see, the problem is not really with the extend function, but rather with the Informix date handling — rather, with the way our calendar months work. Date and datetime handling in Informix may not always be obvious immediately, but it can be quite powerful.