OraFAQ Forum: Training & Certification » Comparing date from select statement

Home » Other » Training & Certification » Comparing date from select statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Comparing date from select statement [message #271187]

Sun, 30 September 2007 11:27

puriyves
Messages: 5
Registered: September 2007
Location: Indonesia

Junior Member

Hi all,

I have some newbie question in PL/SQL,
how to compare two date in PL/SQL, I try to compare two date from select statement, example :

my 1st date is from "SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL"
my 2nd date is from "SELECT DATE FROM A"

I need to compare this two and if the date have the same value it will execute "SELECT EMPLOYEE FROM B",

and if the date return with different value it will execute
"SELECT EMPLOYEE FROM C",

can I use IF or CASE statement with SELECT statement ?
or I need some variable to execute this command ?

maybe someone can give me an example to complete this task,

Thank you in advance

Report message to a moderator

Re: Comparing date from select statement [message #271188 is a reply to message #271187]

Sun, 30 September 2007 11:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Based upon your post I can only conclude that you are trying to learn PL/SQL without ever opening the manual.
In case you could not find it, here is the manual that holds the answer(s) to your PL/SQL question(s).

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Report message to a moderator

Re: Comparing date from select statement [message #271197 is a reply to message #271187]

Sun, 30 September 2007 14:30

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

my 1st date is from "SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL"

No, this is NOT your first date; it is a STRING (CHARACTER datatype).

Quote:

my 2nd date is from "SELECT DATE FROM A"

Assuming that 'a.date' is of the DATE datatype, yes - it is your 'second date'.

So, if you want to compare dates, do so! SYSDATE is a function and returns DATE datatype; you'd use TO_CHAR function to format this date and display it as you'd like it to.

Now, reading the whole PL/SQL User's Guide will take some time. So here it is: create two variables: first of them to store result of one of your queries (modified so that they BOTH return DATE); another one to store result of the SELECT statement, returning a value from table 'B' or table 'C'. There are ways to do that, but a straightforward one is

select date_column into var_1 from a;

if var_1 = sysdate then
   select something into var_2 from b;
else
   select something into var_2 from c;
end if;

Report message to a moderator