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 Go to next message
hissam78
Messages: 186
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

Re: Update difference between two dates in months [message #686736 is a reply to message #686735] Thu, 08 December 2022 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68348
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain the VALUE values in your output as you said: "if we select :D1 = 01-apr-2022 then Weaving machine value should not calculate because its date is 01-01-2022" and your output shows 9 for this row.
What value did you then choose for this output?
Re: Update difference between two dates in months [message #686737 is a reply to message #686736] Thu, 08 December 2022 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68348
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It would be clearer to say that VALUE is the difference (in months) between p_date and :D2!

SQL> var d1 varchar2(20)
SQL> var d2 varchar2(20)
SQL> exec :d1 := '01-APR-2022'; :d2 := '30-DEC-2022'

PL/SQL procedure successfully completed.

SQL> -- What should be updated
SQL> select t.*,
  2         to_char(months_between(to_date(:d2,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),p_date),
  3                 '99990.00')
  4           new_value
  5  from test1 t
  6  where p_date between :d1 and :d2
  7  union all
  8  -- what should not be updated
  9  select t.*, null
 10  from test1 t
 11  where p_date not between :d1 and :d2
 12  /
TITLE                    AMOUNT      VALUE P_DATE            RATE FINANCIAL_ NEW_VALUE
-------------------- ---------- ---------- ----------- ---------- ---------- ---------
CHAIR                     28000            07-APR-2022         33 2021-22         8.74
LAPTOP                    40000            07-JUN-2022         33 2021-22         6.74
BUS                     2000000            01-SEP-2022         33 2021-22         3.94
TABLE                     50000            01-MAY-2022         33 2021-22         7.94
WEAVING MACHINE        10000000            01-JAN-2022         33 2021-22

5 rows selected.
I let you write the UPDATE statement from this.
Re: Update difference between two dates in months [message #686744 is a reply to message #686737] Fri, 09 December 2022 18:10 Go to previous message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Thanks a lot Dear Michel Cadot,
it works, grateful to you for your all kindness and support.

regards,
Previous Topic: Value multiple
Next Topic: String Extract using REGEXP_SUBSTR
Goto Forum:
  


Current Time: Sun Feb 05 13:50:05 CST 2023