Home » SQL & PL/SQL » SQL & PL/SQL » Update difference between two dates in months (Oracle 11 G, Windows)
Update difference between two dates in months [message #686735] |
Wed, 07 December 2022 17:06 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts,
we have a table test1 with create and insert query, we need to update Test1 table column named VALUE with months by
calculating between to dates.
e.g. we have
P_DATE BETWEEN :D1 AND :D2
:D1 = 01-APR-2022
:D2 = 30-DEC-2022
SO DIFFERENCE BETWEEN :D1 AND :D2 RETURN IN NUMBER OF MONTHS IN VALUE COLUMN AS SHOWN BELOW IN OUTPUT,
Somebody can help please to make the UPDATE Statement. if we select :D1 = 01-apr-2022 then Weaving machine value should not calculate
because its date is 01-01-2022
CREATE TABLE "TEST1"
( "TITLE" VARCHAR2(199 BYTE),
"AMOUNT" NUMBER,
"VALUE" NUMBER,
"P_DATE" DATE,
"RATE" NUMBER,
"FINANCIAL_YEAR" VARCHAR2(500 BYTE)
)
REM INSERTING into TEST1
Insert into TEST1 (TITLE,AMOUNT,VALUE,P_DATE,RATE,FINANCIAL_YEAR) values ('CHAIR',28000,null,to_timestamp('07-APR-22','DD-MON-RR HH.MI.SSXFF AM'),33,'2021-22');
Insert into TEST1 (TITLE,AMOUNT,VALUE,P_DATE,RATE,FINANCIAL_YEAR) values ('LAPTOP',40000,null,to_timestamp('07-JUN-22','DD-MON-RR HH.MI.SSXFF AM'),33,'2021-22');
Insert into TEST1 (TITLE,AMOUNT,VALUE,P_DATE,RATE,FINANCIAL_YEAR) values ('BUS',2000000,null,to_timestamp('01-SEP-22','DD-MON-RR HH.MI.SSXFF AM'),33,'2021-22');
Insert into TEST1 (TITLE,AMOUNT,VALUE,P_DATE,RATE,FINANCIAL_YEAR) values ('WEAVING MACHINE',10000000,null,to_timestamp('01-JAN-22','DD-MON-RR HH.MI.SSXFF AM'),33,'2021-22');
Insert into TEST1 (TITLE,AMOUNT,VALUE,P_DATE,RATE,FINANCIAL_YEAR) values ('TABLE',50000,null,to_timestamp('01-MAY-22','DD-MON-RR HH.MI.SSXFF AM'),33,'2021-22');
COMMIT;
REM OUTPUT:
TITLE AMOUNT VALUE P_DATE RATE FINANCIAL_YEAR
CHAIR 28000 9 07-APR-22 33 2021-22
LAPTOP 40000 7 07-JUN-22 33 2021-22
BUS 2000000 4 01-SEP-22 33 2021-22
WEAVING MACHINE 100000000 12 1-JAN-22 33 2021-22
TABLE 50000 8 01-MAY-22 33 2021-22
Thanks,
Regards,
[Updated on: Wed, 07 December 2022 17:28] Report message to a moderator
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:51:47 CDT 2024
|