Difference between Varchar dates [message #1148] |
Thu, 04 April 2002 12:43 |
jacks jason
Messages: 1 Registered: April 2002
|
Junior Member |
|
|
TABLE: EMP_SALARY(EMPNO NUMBER,
SALARY NUMBER,
PERIOD VARCHAR2)
RECORDS:
EMPNO SALARY PERIOD
------------------------
10 2800 JAN-2002
10 3000 FEB-2002
10 4000 MAR-2002
10 4200 APR-2002
I need to display records based on the difference between current month and previous month for each employee. How to achieve this?
|
|
|
Re: Difference between Varchar dates [message #1160 is a reply to message #1148] |
Sat, 06 April 2002 14:16 |
Jay
Messages: 127 Registered: October 1999
|
Senior Member |
|
|
I hope this is what you are trying to achieve:
CREATE TABLE emp_salary
(empno number(3),
salary number(7,2),
period date);
Table created.
insert into emp_salary
(empno,salary,period)
values
(10,2800,to_date('Jan-2002','Mon-YYYY'));
insert into emp_salary
(empno,salary,period)
values
(10,3000,to_date('Feb-2002','Mon-YYYY'));
insert into emp_salary
(empno,salary,period)
values
(10,4000,to_date('Mar-2002','Mon-YYYY'));
insert into emp_salary
(empno,salary,period)
values
(10,4200,to_date('Apr-2002','Mon-YYYY'));
select empno,salary,period,
to_char(sysdate,'MM')-to_char(period,'MM')
records
from emp_salary
order by period;
1 row created.
1 row created.
1 row created.
1 row created.
EMPNO SALARY PERIOD RECORDS
---------- ---------- --------- ----------
10 2800 01-JAN-02 3
10 3000 01-FEB-02 2
10 4000 01-MAR-02 1
10 4200 01-APR-02 0
|
|
|