Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: date comparison problem
Oracle uses two methods for comparing characters: Blank-padding
character semantics and nonpadded character semantics. Blank padding is
only used when comparing fixed length characters as in your first
example. If either side of the operand is varying then the nonpadded
method is used. Therefore for the first example to work add two blanks
to the end of date format 'yymmdd '
Michael Rothwell wrote:
> 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 Mon Feb 01 1999 - 03:35:13 CST