Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: date comparison problem

Re: date comparison problem

From: Mike Burden <michael.burden_at_capgemini.co.uk>
Date: Mon, 01 Feb 1999 09:35:13 +0000
Message-ID: <36B57550.44C2DB55@capgemini.co.uk>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US