Re: date comparison problem

Hi Michael,

EE_DATE is CHAR and not VARCHAR2. The date in this field looks like '981231 ' and is greather than '981231'.
Try :

select distinct ee_date from engagement_expense  where EE_DATE <= rpad(to_char( add_months( last_day( trunc( sysdate ) ),-1 ),'yymmdd' ),8)
order by ee_date

HTH
Philippe

Michael Rothwell wrote in message
<36A532D0.DB32695D_at_non-hp-usa-om46.om.hp.com>...
>I have a table ENGAGEMENT_EXPENSE that has a column EE_DATE
>that is currently a CHAR(8) (this will be changed to a DATE
>type in the next two months).
>
>When I run the following SQL against the table
>
>select distinct ee_date from engagement_expense
> where EE_DATE <= to_char( add_months( last_day( trunc(
>sysdate ) ),-1 ),'yymmdd' )
> order by ee_date
>
>I get all of the dates except '981231'
>
>However I do get that date when I replace the WHERE clause
>as follows:
>
>select distinct ee_date from engagement_expense
> where EE_DATE <= '981231'
> order by ee_date
>
>or when I use:
>
>select distinct ee_date from engagement_expense
> where to_date(EE_DATE, 'YYMMDD') <=
>last_day(add_months(sysdate,-1) )
> order by ee_date
>
>What happens to 981231 in the first example??
>
>Michael
>--
>Michael A. Rothwell
>Oracle DBA/Web Developer
>
>Views expressed here are not those of my company - No - Wait
>- Since I am independent, I guess they are the views of my
>company.
Received on Wed Jan 20 1999 - 08:44:01 CST