更新Oracle 11g中日期之间的月数

g6baxovj 于 2022-12-11 发布在 Oracle

关注(0)|答案(1)|浏览(144)

We have a table test1 , we need to update the VALUE column by calculating the months between two dates. E.g. we have

P_DATE BETWEEN :D1 AND :D2

:D1 = 01-APR-2022
:D2 = 30-DEC-2022

So calculate the difference between :D1 and :D2 and return the number of months in the VALUE column. Can somebody please help to make the UPDATE Statement?

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;

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

oracle

来源：https://stackoverflow.com/questions/74723705/update-number-of-months-between-date-in-oracle-11g

1条答案

按热度按时间

rxztt3cl1#

Use the MONTHS_BETWEEN function and then ROUND to the nearest whole month (or if you want to always round up then use CEIL instead of ROUND ):

UPDATE test1
SET value = ROUND(MONTHS_BETWEEN(DATE '2022-12-30', p_date));

Which, for your sample data, then after the update the table contains:
| TITLE | AMOUNT | VALUE | P_DATE | RATE | FINANCIAL_YEAR |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CHAIR | 28000 | 9 | 2022-04-07 00:00:00 | 33 | 2021-22 |
| LAPTOP | 40000 | 7 | 2022-06-07 00:00:00 | 33 | 2021-22 |
| BUS | 2000000 | 4 | 2022-09-01 00:00:00 | 33 | 2021-22 |
| WEAVING MACHINE | 10000000 | 12 | 2022-01-01 00:00:00 | 33 | 2021-22 |
| TABLE | 50000 | 8 | 2022-05-01 00:00:00 | 33 | 2021-22 |
fiddle

Update

If you only want to update the values when P_DATE is within a certain range then use:

UPDATE test1
SET   value = ROUND(MONTHS_BETWEEN(DATE '2022-12-30', p_date))
WHERE p_date BETWEEN DATE '2022-04-01' AND DATE '2022-12-30';

or, if you are using bind variables then:

UPDATE test1
SET   value = ROUND(MONTHS_BETWEEN(:D2, p_date))
WHERE p_date BETWEEN :D1 AND :D2;

Which, for the sample data, after the update the table contains:
| TITLE | AMOUNT | VALUE | P_DATE | RATE | FINANCIAL_YEAR |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CHAIR | 28000 | 9 | 2022-04-07 00:00:00 | 33 | 2021-22 |
| LAPTOP | 40000 | 7 | 2022-06-07 00:00:00 | 33 | 2021-22 |
| BUS | 2000000 | 4 | 2022-09-01 00:00:00 | 33 | 2021-22 |
| WEAVING MACHINE | 10000000 | null | 2022-01-01 00:00:00 | 33 | 2021-22 |
| TABLE | 50000 | 8 | 2022-05-01 00:00:00 | 33 | 2021-22 |
fiddle

赞(0）回复(0）举报 2022-12-11

我来回答

更新Oracle 11g中日期之间的月数

1条答案

Update

相关问题

热门标签

最新问答