Re: Peculiar results from a simple looking query.

From: <ak_tiredofspam_at_yahoo.com>
Date: 24 Feb 2005 13:07:46 -0800
Message-ID: <1109279266.780711.317840@g14g2000cwa.googlegroups.com>

OK, fine, 'The docs are a nice thing', I completely agree.

Now look at this:

create table aa(c varchar2(30))
Table created

insert into aa values('30-jUn-2004')
1 row inserted

insert into aa values('30-jUn-2007')
1 row inserted

• US locale select sysdate from dual SYSDATE

  ---

  2/24/2005 1:49:06 PM 1 row selected

select c from aa
C

---

30-jUn-2004
30-jUn-2007
2 rows selected

select dump(c) from aa
DUMP(C)

---

Typ=1 Len=11: 51,48,45,106,85,110,45,50,48,48,52

Typ=1 Len=11: 51,48,45,106,85,110,45,50,48,48,55

2 rows selected

• this time the origanal column was selected, not the cast one
• note jUn characters select c from aa where cast(c as date) < sysdate-1 C

  ---

  30-jUn-2004 1 row selected
• this time the origanal column was selected, not the cast one select dump(c) from aa where cast(c as date) < sysdate-1 DUMP(C)

  ---

Typ=1 Len=11: 51,48,45,106,85,110,45,50,48,48,52

1 row selected    

drop table aa
Table dropped  

Do you have any explanations? Received on Thu Feb 24 2005 - 15:07:46 CST