OraFAQ Forum: SQL & PL/SQL » how can we use date in for loop

Home » SQL & PL/SQL » SQL & PL/SQL » how can we use date in for loop

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

how can we use date in for loop [message #238477]

Fri, 18 May 2007 04:29

rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com

Senior Member

How can we use date in for loop ,when i am
using a date for incrementing in a for loop,
it shows error ,Why cant we use date in for loop
any one can solve my query

Thanks in Advance

Have a great Day

Report message to a moderator

Re: how can we use date in for loop [message #238485 is a reply to message #238477]

Fri, 18 May 2007 05:32

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

Can u post the code which u were trying?

Report message to a moderator

Re: how can we use date in for loop [message #238486 is a reply to message #238477]

Fri, 18 May 2007 05:38

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

It would be nice if yuo could show us the code you're having problems with.
If you're trying to do something like:

FOR dt IN sysdate .. sysdate+10 LOOP
...
END LOOP;

then you just can't do it - loops are for numbers, not dates.

You could do:

FOR dt IN 0 .. date_2 - date_1 LOOP
  v_date := date_1+dt;
...
END LOOP;

Report message to a moderator

Re: how can we use date in for loop [message #238585 is a reply to message #238486]

Fri, 18 May 2007 11:14

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

Senior Member
Account Moderator

Or, convert date into a Julian date (numbers) and use those values in the FOR loop.

SQL> BEGIN
  2    FOR i IN TO_NUMBER(TO_CHAR(SYSDATE - 5, 'j')) ..
  3             TO_NUMBER(TO_CHAR(SYSDATE, 'j'))
  4    LOOP
  5       dbms_output.put_line(i);
  6    END LOOP;
  7  END;
  8  /
2454234
2454235
2454236
2454237
2454238
2454239

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: how can we use date in for loop [message #238591 is a reply to message #238585]

Fri, 18 May 2007 11:55

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nice trick! /forum/fa/1581/0/

Regards
Michel

Report message to a moderator

Previous Topic:	trigger and procedure privileges ??!!
Next Topic:	EXISTS/NOT EXIST/ NOT IN/MINUS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 22:08:26 CST 2024