Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Oracle SQL query date problem...

Status
Not open for further replies.

omacron

Technical User
Feb 5, 2002
149
Hi

I am try to compare a VARCHAR field that stores a date as:

MMDDYYYY


To a date time field. So what I am doing is running a script like this;

select
TransactionID,
DateTimeField,
VarCharDate
from Table
where
to_char(DateTimeField, 'MM-DD-YYYY') <> to_char(to_date((VarCharDate), 'MM-DD-YYYY'), 'MM-DD-YYYY')



The problem is that some times when the date is in the future this doesn't work. For example lets say that DateTimeField = 08-29-2009 04:15:23

When doing this: to_char(DateTimeField, 'MM-DD-YYYY') it returns this;

08-29-2008

Why is this happening or is there an easier way of comparing this.
 
Omacron,

First of all, you asked for rows...
Code:
...WHERE...<>...
Isn't it true that "08-29-2008 <> 08-29-2009..."?

When comparing DATEs to DATEs, I recommend you always use DATE-comparison code not character-comparison code:
Code:
select 
  TransactionID,
  DateTimeField,
  VarCharDate
from Table
where trunc(DateTimeField) <> to_date(VarCharDate, 'MM-DD-YYYY')
Let us know if the above code works well for you.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Omacron said:
I am try to compare a VARCHAR field that stores a date as:

MMDDYYYY
Your specification says that the character-DATE values are "MMDDYYYY" format; your code suggests that your character-DATE values are in "MM-DD-YYYY" format. Whichever is the case, your DATE-format mask must match.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Dave,
how about eliminating 1 conversion

Code:
select 
  TransactionID,
  DateTimeField,
  VarCharDate
from Table
where to_char(DateTimeField, 'MMDDYYYY') <> VarCharDate

It may allow using index, if any.

Regards, Dima
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top