Select Max(date1) < date2
Where you have dates in a table: Column Date1 Jan 1, 2000 Mar 1, 2000 May 1, 2000 Jun 1, 2000 Sep 1, 2000 and you want to select the one smaller than date2 (Sep 1, 2000) Select max(date1) from table where date1 < date2 This fails to find Jun 1, 2000. Any ideas? loudelon...
Louis, What DOES it return? If the column is a character column, then it should return May 1, 2000. If it is a full date-time column, maybe it is returning Sep 1, 2000 because the 'time' portions are not exactly the same. Doug "Louis Delongchamp" <loudelon@cyberbeach.net> wrote in message news:39de0098.568739@news.cyberbeach.net... > > Where you have dates in a table: > Column Date1 > Jan 1, 2000 > Mar 1, 2000 > May 1, 2000 > Jun 1, 2000 > Sep 1, 2000 > > and you want to select the one smaller than date2 (Sep 1, 2000) > > Select max(date1) from table where date1 < date2 > > This fails to find Jun 1, 2000. Any ideas? > > loudelon...
Louis Delongchamp wrote:
>
> Where you have dates in a table:
> Column Date1
> Jan 1, 2000
> Mar 1, 2000
> May 1, 2000
> Jun 1, 2000
> Sep 1, 2000
>
> and you want to select the one smaller than date2 (Sep 1, 2000)
>
> Select max(date1) from table where date1 < date2
>
> This fails to find Jun 1, 2000. Any ideas?
Funny; it works for me:
create temp table t (d date);
insert into t values('01/01/2000');
insert into t values('01/03/2000');
insert into t values('01/06/2000');
insert into t values('01/05/2000');
insert into t values('01/09/2000');
select * from t;01/01/2000
01/03/2000
01/06/2000
01/05/2000
01/09/2000
select max(d) from t where d < date('01/09/2000');
01/06/2000
Of course, I have DBDATE=dmy4/, and I'm using CSDK 2.40 on Solaris 7
against Foundation 9.21.UC1.
Also, I'm dealing with a one-column table. If you had other columns in
there, you might have to worry about grouping etc.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
Sorry, wrong group! This is a VB6 Access 2000 question for microsoft.public.vb.database Value returned is null On Fri, 6 Oct 2000 14:02:04 -0400, "Doug Agnew" <dagnew@charlottepipe.com> wrote: >Louis, > >What DOES it return? If the column is a character column, then it should >return May 1, 2000. If it is a full date-time column, maybe it is returning >Sep 1, 2000 because the 'time' portions are not exactly the same. > >Doug >"Louis Delongchamp" <loudelon@cyberbeach.net> wrote in message >news:39de0098.568739@news.cyberbeach.net... >> >> Where you have dates in a table: >> Column Date1 >> Jan 1, 2000 >> Mar 1, 2000 >> May 1, 2000 >> Jun 1, 2000 >> Sep 1, 2000 >> >> and you want to select the one smaller than date2 (Sep 1, 2000) >> >> Select max(date1) from table where date1 < date2 >> >> This fails to find Jun 1, 2000. Any ideas? >> >> loudelon... > > loudelon...
Is the data set up as DATE, or as (say) CHAR( 12 )?
Just looking at your sample data, an ASCII sort would return:
Jan 1, 2000
Jun 1, 2000
Mar 1, 2000
May 1, 2000
Sep 1, 2000
To test , try
SELECT date1 FROM table
ORDER BY date1;
Select max(date1) from table where date1 < "Mar 1, 2000";
"Jonathan Leffler" <jleffler@informix.com> wrote in message
news:39DE32B1.727B6AA8@informix.com...
> Louis Delongchamp wrote:
> >
> > Where you have dates in a table:
> > Column Date1
> > Jan 1, 2000
> > Mar 1, 2000
> > May 1, 2000
> > Jun 1, 2000
> > Sep 1, 2000
> >
> > and you want to select the one smaller than date2 (Sep 1, 2000)
> >
> > Select max(date1) from table where date1 < date2
> >
> > This fails to find Jun 1, 2000. Any ideas?
>
> Funny; it works for me:
>
> create temp table t (d date);
> insert into t values('01/01/2000');
> insert into t values('01/03/2000');
> insert into t values('01/06/2000');
> insert into t values('01/05/2000');
> insert into t values('01/09/2000');
> select * from t;> 01/01/2000
> 01/03/2000
> 01/06/2000
> 01/05/2000
> 01/09/2000
> select max(d) from t where d < date('01/09/2000');
> 01/06/2000
>
> Of course, I have DBDATE=dmy4/, and I'm using CSDK 2.40 on Solaris 7
> against Foundation 9.21.UC1.
>
> Also, I'm dealing with a one-column table. If you had other columns in
> there, you might have to worry about grouping etc.
>
> --
> Yours,
> Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"