How do I get the difference between a TIMESTAMP and a NUMBER?

I am working on a Hospital database.  The variable 'RMS' represents the charge per day for staying in a hospital room.  I need to repeat the 'RMS' charge for each date till 04/09/2007.  How do I get the difference between 04/09/2007 and Charge_Date?

create table PATIENT_CHARGES(
Patient_ID                  NUMBER NOT NULL,
Charge_Code          VARCHAR2(3),
Charge_Date              TIMESTAMP,
Charge_Value             NUMBER,
constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),
constraint PC_PATIENT_ID_FK foreign key (Patient_ID)
references PATIENT_ADMISSION (Patient_ID),
constraint CHARGE_CODE_FK foreign key (Charge_Code)
references CHARGE_CODE (Charge_Code)
);


insert into PATIENT_CHARGES
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');

insert into PATIENT_CHARGES
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');

insert into PATIENT_CHARGES
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');


I have tried the following and I get these errors:

update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
where Charge_Code = 'RMS';

update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
                                                                                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE


update PATIENT_CHARGES set Charge_Value = (to_date('04/09/2007','mm/dd/yyyy'))
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';

- Charge_Date * Charge_Value
  *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP


update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007','mm/dd/yyyy'))
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';

update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007','mm/dd/yyyy'))
                                                                                                                                              *
ERROR at line 1:
ORA-00905: missing keyword
CharleneS77Asked:
Who is Participating?
 
mohammadzahidCommented:
update PATIENT_CHARGES set Charge_Value = (to_Date('04092007','mmddyyyy') - to_Date(to_char(charge_date,'mmddyyyy'),'mmddyyyy'))  * Charge_Value
where Charge_Code = 'RMS';
0
 
CharleneS77Author Commented:
Perfect.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.