Home » SQL & PL/SQL » SQL & PL/SQL » Help!
Help! [message #243064] Tue, 05 June 2007 21:01 Go to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hello Experts,

I need to compare a date as per business requirements.
Eg: START_DATE = '01/01/1999
END_DATE = '01/30/9999

now i have to validate some records based on this rule:
if current date(sysdate) between start_date and end_date.

Can some one help me how can i do that in plsql/sql/cursor?

I am actually trying to insert some data from Atable to Btable based on this above condition. I need to find if today's date is between the start_data/end_date.


Thanks in advance,
Tandava




Re: Help! [message #243065 is a reply to message #243064] Tue, 05 June 2007 21:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you expect help with your homework assignment, first you need to show us what you've tried & describe why it is lacking.

Here is a reminder that in both SQL & PL/SQL, string variables are enclosed by single quote marks.

'This is a string' & so is the following: '01/01/1999'

[Updated on: Tue, 05 June 2007 21:26] by Moderator

Report message to a moderator

Re: Help! [message #243072 is a reply to message #243065] Tue, 05 June 2007 23:06 Go to previous messageGo to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hi,

I am very happy to see your immediate response.

I actually tried with anonymous block by declaring the individual variables and trying to compare using months_between for initial testing. But i want it to be generic for my application.

declare
v_curr_date date;
curr_date date default sysdate;
select months_between(start_date, curr_date), 
       months_between(curr_date, start_date)
into v_curr_date
from employer;
dbms_output.put_line(v_curr_date);
end;


I don't have much time to complete the task so i immediately raised a topic. Please help me now.

Thanks,
Tandava
Re: Help! [message #243075 is a reply to message #243064] Tue, 05 June 2007 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Forgive me, while you might clearly understand the problem you are trying to solve, I certainly don't understand it.

What is your table definition?

What date needs to be between which two other dates?

I suggest you start with a simple SELECT statement.
When the SELECT returns the correct results set, then you can use the same criteria for the INSERT.
Re: Help! [message #243078 is a reply to message #243075] Tue, 05 June 2007 23:52 Go to previous messageGo to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hi,

I am very sorry for made you confuse. I did not understand the exact problem. I finally created this block. Please suggest me about this.

declare
v_curr_date date;
curr_date date default sysdate;
start_date date;
end_date date; 
begin
start_date := '01/JAN/1999';
end_date := '30/JAN/9999';
select sysdate
into v_curr_date
from dual;
if start_date < sysdate and end_date > sysdate then
dbms_output.put_line(v_curr_date);
end if;
end;


Thanks,
Tandava
Re: Help! [message #243086 is a reply to message #243078] Wed, 06 June 2007 00:44 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
start_date := '01/JAN/1999';

Is wrong, start_date is declared as date datatype and '01/JAN/1999' is a string as Ana said you.
Use to_date function.

Regards
Michel
Previous Topic: Date field causing duplicate rows in output
Next Topic: CASE conditional statement
Goto Forum:
  


Current Time: Fri Dec 09 19:43:31 CST 2016

Total time taken to generate the page: 0.11287 seconds