date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: date format

  1. #1
    Join Date
    Jun 2000
    Posts
    315
    Can anybody help me with this?


    INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,to_date(fdate,'MM/DD/YYYY'),weektype,gid);

    Oracle gives me an error. Here's what I am trying to do. Find out how many weeks between the sdate and fdate then duplicate data based on mdate between the 2 dates. If I hardcode cnt, I can get it to insert the values, though I still need to figure out why I can't format the date model in the insert statement. I need something like this:

    INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,to_date(fdate,'MM/DD/YYYY')+(7*cnt),weektype,gid);

    Each of the dates are for every month of the month. Table schema is here:
    Thanks.




    CREATE TABLE HOURLY_MP_WEEK1 (
    ID NUMBER (5) NOT NULL,
    WEEK DATE NOT NULL,
    WEEK_TYPE VARCHAR2 (2) NOT NULL,
    GROUP_ID NUMBER (2),
    CHECK (week_type in ('D','Sa','Su')),
    CONSTRAINT PK_HOURLYMPWEEK1
    PRIMARY KEY ( ID )
    USING INDEX
    TABLESPACE USERS PCTFREE 10
    STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 50 ))
    TABLESPACE USERS
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 10240
    NEXT 10240
    PCTINCREASE 50
    MINEXTENTS 1
    MAXEXTENTS 121
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;


    insert into hourly_mp_week1 (id,week,week_type,group_id) values (200,'11/05/2001','D',1);
    insert into hourly_mp_week1 (id,week,week_type,group_id) values (201,'11/05/2001','Sa',1);
    insert into hourly_mp_week1 (id,week,week_type,group_id) values (202,'11/05/2001','Su',1);



    CREATE OR REPLACE PROCEDURE REPLICATE_DATA(
    gid number,
    mdate date,
    fdate date,
    tdate date)
    AS
    id hourly_mp_week1.id%TYPE;
    weektype hourly_mp_week1.week_type%TYPE;
    cnt number(2);
    i number;
    CURSOR c1 IS
    SELECT id,week_type FROM hourly_mp_week1
    WHERE to_char(week,'MM/DD/YYYY')='11/05/2001';
    CURSOR c2 IS
    SELECT to_number(to_char((to_date('12/17/2001','MM/DD/YYYY')-to_date('12/03/2001','MM/DD/YYYY'))/7)) FROM dual;

    BEGIN
    OPEN c2;
    LOOP
    FETCH c2 INTO cnt;
    EXIT WHEN c2%NOTFOUND;
    END LOOP;
    CLOSE c2;

    OPEN c1;
    LOOP
    FETCH c1 INTO id,weektype;
    EXIT WHEN c1%NOTFOUND;

    FOR i IN 0..cnt LOOP
    /* need to vary the fdate also, but I'm still working on it */
    INSERT INTO hourly_mp_week1 VALUES (seq_temp.nextval,fdate,weektype,gid);
    END LOOP;

    END LOOP;
    CLOSE c1;
    COMMIT;

    END REPLICATE_DATA;



  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It's not clear exactly what you are trying to do, but I think an example is in order:
    Code:
    SQL> create table xyz (x date);
    Table created.
    
      1  declare
      2     i integer;
      3  begin
      4     for i in 1..10 loop
      5        insert into xyz values (sysdate+i);
      6     end loop;
      7     commit work;
      8* end;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from xyz;
    
    X
    ---------
    18-OCT-01
    19-OCT-01
    20-OCT-01
    21-OCT-01
    22-OCT-01
    23-OCT-01
    24-OCT-01
    25-OCT-01
    26-OCT-01
    27-OCT-01
    
    10 rows selected.
    
    SQL>
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2001
    Posts
    122

    Question

    What is the exact error you are getting?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width