Re: Using date in WHERE clause in Informix

[Rick_Creel@AONCONS.COM]

How about this...

begin-procedure main
Let $date1 = datetostr($current-date,'MM/DD/YYYY')
let $low_dt = dateadd($date1,'day',-1)
let $high_dt = dateadd($date1,'day',1)
begin-select
a.DATE
    Do something
from TABLE_NAME a
where a.date > $low_dt
and  a.date < $high_dt
end-select





"jayateerth.rp" <jayateerth.rp@MPHASIS.COM> on 05/03/2001 10:35:54 AM

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Rick Creel/IT/Aon Consulting)

Subject:  Using date in WHERE clause in Informix



Dear Friends,
I want to extract all the records that were inserted for the day from a
table called "TABLE_NAME". The date on which the records were inserted
will be stored in a field called DATE. But since the database used is
Informix, the dates are stored in the format YYYY-MM-DD
HH:MI:SS.NNN.(i.e. It will contain both date and time)

The code, which I wrote to extract is as follows...

begin-program
  do main
end-program

begin-procedure main
Let $date1 = datetostr($current-date,'MM/DD/YYYY')
begin-select
Let $date2 = datetostr(&a.DATE,'MM/DD/YYYY')
If $date1 = $date2
    Do something
End-if
a.DATE
from TABLE_NAME a
end-select
end-procedure

But this is not efficient because I am extracting all the records and
then considering the for the day records.
I would like to use this condition in where clause to improve the
performance. So I changed my code like .....

begin-program
  do main
end-program
begin-procedure main
Let $date1 = datetostr($current-date, 'MM/DD/YYYY')
begin-select
a.DATE
from TABLE_NAME a where
    datetostr(a.DATE,'MM/DD/YYYY') = [$date1]
end-select
end-procedure


But I am getting errors while compiling. Can any one suggest me to
include date part in WHERE clause.

With Thanks,
Jay
.