Mega Code Archive

 

Categories / Oracle PLSQL / Select Query

 

Convert string based date variable to date type during when comparing

SQL> -- create demo table SQL> create table emp(   2    ID                 VARCHAR2(4 BYTE)         NOT NULL,   3    fname         VARCHAR2(10 BYTE),   4    lname          VARCHAR2(10 BYTE),   5    Start_Date         DATE,   6    End_Date           DATE,   7    Salary             Number(8,2),   8    City               VARCHAR2(10 BYTE),   9    Description        VARCHAR2(15 BYTE)  10  )  11  / Table created. SQL> SQL> SQL> SQL> -- prepare data SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date ,                       Salary,  City,       Description)   2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMM DD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')   3  / 1 row created. SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date ,                       Salary,  City,       Description)   2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMM DD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')   3  / 1 row created. SQL> SQL> SQL> declare   2    dateValue date;   3    s Number(8,2);   4    begin   5         select start_date into dateValue from emp where rownum = 1;   6         select salary into s from emp where rownum = 1;   7   8          IF dateValue > '11-APR-63' then   9              s :=  s * 1.15;  10          END IF;  11  12  13       dbms_output.put_line(s);  14  end;  15  / 1419.74 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL>