compare date + PL/SQL

Hi, I have a question about comparing date in PL/SQL.
I have a query:
select * from table1 where TO_CHAR(eff_date,'MM/DD/YYYY') < TO_CHAR(CURRENT_DATE,'MM/DD/YYYY')
When I run this query, I only got 5 records, I am expecting 7 records. The other 2 records is not showing up is because the month is 12, which is greater than the current_date month , which is 10.
Is there another way to compare date?

Thanks in advance.
chenjhuaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select * from table1 where eff_date < TRUNC(SYSDATE)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to explain what goes wrong in yours, is the that format MM/DD/YYYY is a bad choice to compare with < or >

to make it work with TO_CHAR, you need the order YMD for the format:
select * from table1 where TO_CHAR(eff_date,'YYYY/MM/DD') < TO_CHAR(CURRENT_DATE,'YYYY/MM/DD')

0
 
chenjhuaAuthor Commented:
angelIII:
It works, Thank you very much for your help. Can I ask you another quick question? I only need know if there is record or not, so I can return true or false back, So I don't need use select count(*), which the performance is not good, I should use:
select 1 from table1 where eff_date < TRUNC(SYSDATE)
Rght? The performance will be better?

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I only need know if there is record or not
in what context?

IF EXISTS ( select ... from ... where ... ) THEN
  -- do something --
END IF;
0
 
chenjhuaAuthor Commented:
Which performance will be better?

IF EXISTS (SELECT * FROM ... WHERE... ) THEN
OR
IF EXISTS (SELECT 1 FROM .... WHERE ....) THEN

THANKS
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in short: no difference.
actually the SELECT * will eventually take a dictionnary lookup more to expand the * into the list of columns, unless oracle optimized it in the context of EXISTS() as it is not necessary...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.