Number of days in a month
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.
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/