Home » SQL & PL/SQL » SQL & PL/SQL » How select all days of specific month from dual ??? (Oracle 10g ..... win XP SP2)
icon5.gif  How select all days of specific month from dual ??? [message #289879] Wed, 26 December 2007 06:44 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

How select all days of specific month from dual ???

and that what i made :-

create table month(day date);
--------------
begin
for i in '01-Nov-07'..'01-Dec-07' loop
insert into month values(i);
end loop;
end;
/


insert into month values(i);
                         *
ERROR at line 3:
ORA-06550: line 3, column 26:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NATIVE INTEGER
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


I want solve for this problem , and thanks in advance
Re: How select all days of specific month from dual ??? [message #289884 is a reply to message #289879] Wed, 26 December 2007 06:57 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:

create table month(day date);
--------------
begin
for i in '01-Nov-07'..'01-Dec-07' loop
insert into month values(i);
end loop;
end;
/


insert into month values(i);
*
ERROR at line 3:
ORA-06550: line 3, column 26:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NATIVE INTEGER
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


I want to know if possible to use

for i in '01-Nov-07'..'01-Dec-07'loop this format ? Click Here
Re: How select all days of specific month from dual ??? [message #289886 is a reply to message #289879] Wed, 26 December 2007 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 4 Using PL/SQL Control Structures
Section Controlling Loop Iterations: LOOP and EXIT Statements
Subsection Using the FOR-LOOP Statement
Quote:

Simple FOR loops iterate over a specified range of integers.


By the way, '01-Nov-07' is not a date it is a string.

Regards
Michel
Re: How select all days of specific month from dual ??? [message #289894 is a reply to message #289886] Wed, 26 December 2007 07:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hany, you should know (after 100 posts) that dates are not the same as strings in Oracle, but I'm not going to enter that discussion now. What you are looking for is a row generator. Search for it. Once you have that, you will be able to write a single insert statement. No loops, no PL/SQL.

Here's one old thread already.

MHE
Re: How select all days of specific month from dual ??? [message #290083 is a reply to message #289879] Thu, 27 December 2007 06:07 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

How select all days of specific month from dual ???


Check this one also, you have to change the DATEs range in WHERE condition for other months.

WITH DATES AS
     (SELECT TO_DATE ('01-01-07', 'DD-MM-RR') + L DD
        FROM (SELECT     LEVEL L
                    FROM DUAL
              CONNECT BY LEVEL < 1000) CAL)
SELECT DD
  FROM DATES
 WHERE DD BETWEEN TO_DATE ('01122007', 'ddmmyyyy') AND TO_DATE ('31122007', 'ddmmyyyy')


Regards,
Kiran.
Re: How select all days of specific month from dual ??? [message #290088 is a reply to message #290083] Thu, 27 December 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't you say you will no more spoon feeding?
Maarten's link should be sufficient to OP to at least start if not find a solution above all with "row generator" keywords.

Regards
Michel
Re: How select all days of specific month from dual ??? [message #290145 is a reply to message #289879] Thu, 27 December 2007 12:49 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Since he was spoon fed already, following is a version that will work on any version of oracle.

SELECT TO_DATE('01/01/2007', 'DD/MM/YYYY') + (ROWNUM - 1)
FROM ALL_OBJECTS
WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('01/01/2007', 'DD/MM/YYYY')),'DD'))
Re: How select all days of specific month from dual ??? [message #290327 is a reply to message #289879] Fri, 28 December 2007 06:50 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hmmmmm..
Previous Topic: How to share a context
Next Topic: PL/SQL procedures doubt
Goto Forum:
  


Current Time: Thu Dec 08 14:32:24 CST 2016

Total time taken to generate the page: 0.17073 seconds