I have a monthly amount that I need to spread equally over the number of days in the month. The data looks like this:
Month Value
----------- ---------------
01-Jan-2012 100000
01-Feb-2012 121002
01-Mar-2012 123123
01-Apr-2012 118239
I have to spread the Jan amount over 31 days, the Feb amount over 29 days and the March amount over 31 days.
How can I use PL/SQL to find out how many days there are in the month given in the month column?
SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) num_of_days FROM dual;
/
SELECT SYSDATE, TO_CHAR(LAST_DAY(SYSDATE), 'DD') num_of_days FROM dual
/
-- Days left in a month --
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days left"
FROM DUAL
/
SELECT CAST(to_char(LAST_DAY(date_column),'dd') AS INT)
FROM table1
Don't use to_char()
and stuff when doing arithmetics with dates.
Strings are strings and dates are dates. Please respect the data types and use this instead:
1+trunc(last_day(date_column))-trunc(date_column,'MM')
Indeed, this is correct. It computes the difference between the value of the last day of the month and the value of the first (which is obviously always 1 and therefore we need to add this 1 again).
You must not forget to use the trunc()
function if your date columns contains time, because last_day()
preserves the time component.
You can add a month and subtract the two dates
SQL> ed
Wrote file afiedt.buf
1 with x as (
2 select date '2012-01-01' dt from dual union all
3 select date '2012-02-01' from dual union all
4 select date '2012-03-01' from dual union all
5 select date '2012-01-31' from dual
6 )
7 select dt, add_months(trunc(dt,'MM'),1) - trunc(dt,'MM')
8* from x
SQL> /
DT ADD_MONTHS(TRUNC(DT,'MM'),1)-TRUNC(DT,'MM')
--------- -------------------------------------------
01-JAN-12 31
01-FEB-12 29
01-MAR-12 31
31-JAN-12 31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With