Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Informix
Expertise: Beginner
Mar 14, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Two questions on extend function/how Informix handles dates

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 can extract 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.

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),
        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
                  let p_new_day = 29
                end if
                let p_result = mdy (p_new_month, p_new_day,
    end case  {* p_new_month *}
    let p_result = p_date + p_months units month
  end if

  return p_result

end function {* add_months_to_date *}

If you want to, you can create a stored procedure to do this, and then 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_date
end function  {* Last_Day_of_Month *}
The nice thing about this second function is that it is going to be 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.
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date