Home » SQL & PL/SQL » SQL & PL/SQL » how can we use date in for loop
icon6.gif  how can we use date in for loop [message #238477] Fri, 18 May 2007 04:29 Go to next message
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
Re: how can we use date in for loop [message #238485 is a reply to message #238477] Fri, 18 May 2007 05:32 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Can u post the code which u were trying?
Re: how can we use date in for loop [message #238486 is a reply to message #238477] Fri, 18 May 2007 05:38 Go to previous messageGo to next message
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;
Re: how can we use date in for loop [message #238585 is a reply to message #238486] Fri, 18 May 2007 11:14 Go to previous messageGo to next message
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>
Re: how can we use date in for loop [message #238591 is a reply to message #238585] Fri, 18 May 2007 11:55 Go to previous message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Nice trick! /forum/fa/1581/0/

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


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