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?

Solution 1

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.

Solution 2

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

Solution 3

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
/

Solution 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

Solution 5

select add_months(my_date, 1)-my_date from dual;

View more solutions

Comments

[Raj More]

try the same thing using 2012-01-31. You get 29 instead of 31, which is incorrect.

[Justin Cave]

@RajMore - I'm not sure that I understand your point. If you add 1 month to 2012-01-31, that gives you 2012-02-29. Subtracting the two gives 29 which is correct. Of course, that gives you the number of days in February, not January. The sample data you posted were all from the first of the month, though, not the last day of the month. You can trunc(some_date, 'MM') to convert the date to the first of the month in order to match the sample data that you posted.

[Raj More]

I just put together sample data for the sake of an example - the data is actually spread all over the month.

[Justin Cave]

@RajMore - When you're putting together sample data, please try to cover as much as possible the actual use cases that you need to handle. If your sample data is all on the first and you don't specify that the real data occurs throughout the month, folks will naturally assume that you are just storing the month in your data. I updated my answer to include the trunc.

[UniversE]

This is wrong. Consider 2016-10-31 as input. It returns 30 days.

[Sauer]

Wow, this is what I call "pythonic" :)

[RET]

The question clearly shows that the date will always be the first of the month, so this is a valid answer. If you have a different requirement you can do select add_months(TRUNC(my_date,'MM'), 1)-TRUNC(my_date,'MM') from dual;