Re: query on date

Christoph Wagner wrote:
>
> This one is OK but if there is an index on the column date_of_death it would
> not be used, because index is not used if you perform functions on such
> columns.
>
> This one would be OK for an index scan.
>
> SELECT *
> FROM patient
> WHERE date_of_death BETWEEN to_date('01-JAN-'||param,'DD-MON-YY')
> AND
> to_date('31-DEC-'||param,'DD-MON-YY');
>
> Also mind the 4-Digit YEAR ('YYYY');
>
> greetings Christoph
>
> simon cunningham <cs694_at_gre.ac.uk> schrieb in im Newsbeitrag:
> 38E48CA0.990C8BAF_at_gre.ac.uk...
> > Hi
> >
> > I have a table with the date_of_death as a column dd-mm-yy.
> > Now I wan to run a select statement from this column, where the clause
> > is on the year,
> >
> > So
> > select *
> > from patient
> > where (trunc ('date_of_death', 'YY') = '94'
> > file://where YY is year
> > Is this the corect way to run this query
> >
> > Thanks everyone
> >

Good advice, but be mindful of boundary conditions. This query will not return those who died on 31-Dec because TO_CHAR(date) return the time representing midnight of that date unless you spell out the HH24:MI:SS as well. Make the '01-JAN-'||param+1 to make it inclusive of the 31-Dec.

John Gasch Received on Fri Mar 31 2000 - 08:43:44 CST