Number of days in a month

Question

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?

Answer 1

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
/

Answer 2

SELECT CAST(to_char(LAST_DAY(date_column),'dd') AS INT)
  FROM table1

Answer 3

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.

Answer 4

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