5 Replies Latest reply: Nov 30, 2012 2:16 AM by MaheshGx RSS

    Fetching data from table using date condition

    MaheshGx
      Hi,

      I have a table structure and data as below.

      create table production
      (
      IPC VARCHAR2(200),
      PRODUCTIONDATE VARCHAR2(200) ,
      QUANTITY VARCHAR2(2000),
      PRODUCTIONCODE VARCHAR2(2000),
      MOULDQUANTITY VARCHAR2(2000));

      insert into production
      values ('1111','20121119','1023','AAB77','0002');

      insert into production
      values ('1111','20121122','1023','AAB77','0002');

      insert into production
      values ('1111','20121126','1023','AAB77','0002');

      insert into production
      values ('1111','20121127','1023','AAB77','0002');

      insert into production
      values ('1111','20121128','1023','AAB77','0002');

      insert into production
      values ('1111','20121201','1023','AAB77','0002');

      insert into production
      values ('1111','20121203','1023','AAB77','0002');

      insert into production
      values ('1111','20121203','1023','AAB77','0002');

      insert into production
      values ('1111','20130103','1023','AAB77','0002');

      insert into production
      values ('1111','20130104','1023','AAB77','0002');

      insert into production
      values ('1111','20130105','1023','AAB77','0002');


      Now here i want to fetch data having condition as

      PRODUCTIONDATE  >= Monday of current week

      so i would skip only first two rows and will have to get all rows.

      I tried using below condition but it would give not give data for 2013 values.

      to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))

      Any help would be appreciated.

      Thanks,
      Mahesh