Re: (Q) Subtract 6 months from a date: result=error -1267!!!
From Quentin North:
*
* What we have is a date column that contains a complete year to day
* date. This column needs to specify dates including the day in month.
* However, we wish to do a select on this column producing the last 6
* months rows to a specific day. Hence our select looks like:
*
* select * from tab
* where datecol between date(current) - 6 units month and date(current)
*
* This should produce all rows within 6 months to the day.
* ^^^^^^^^^^
*
* What we want is the following results:
*
* (current date) (current-6 months)
* 1994-03-15 1993-09-15
* 1994-03-31 1993-09-30
*
* Similarly this problem occurs on a leap year (1994-02-29) if you
* subtract anything other than four years using (- units years).
*
* How can we achieve what we want with informix?
I think I might have your answer. Try this and let me know.
select * from tab
where datecol between
( date( mdy( month( today ), 1, year( today ) ) ) - 5 units month )
- 1 units day
and
date(current)
Robert Minter |Data Systems Support| \\\\\\_///
Programmer, Software Development | Orange, CA | ( _ _ )
internet: rob@dssmktg.com | Tel: 714.771.0454 | (| ^ |)
bangpath: uunet.uu.net!dssmktg!rob| Fax: 714.771.3028 | \\`-'/
#include <disclaimer.h> \\_/