• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8777
  • Last Modified:

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.
0
chenjhua
Asked:
chenjhua
  • 4
  • 2
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now