Oracle date computing

Oracle date computing

The Code is as follows:

select to_number(add_months(trunc(to_date('2014-11-4 11:13:53','yyyy-mm-dd hh24:mi:ss'),'mm'),1) - trunc(to_date('2014-11-4 11:13:53', 'yyyy-mm-dd hh24:mi:ss'),'mm')) daycountfrom dual;select to_number(add_months(  trunc(to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),'mm'),1) -   trunc(to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'mm')) dayscount  from dual;

SELECT add_months(trunc(sysdate,'mm'),1)   -   trunc(sysdate,'mm') from dual;

The code for querying the last day of the month in oracle is as follows:

Select to_char (trunc (add_months (last_day (sysdate),-1) + 1), 'yyyy-mm-dd') "first day of the month", to_char (last_day (sysdate ), 'yyyy-mm-dd') "last day of the month" from dual; SELECT to_char (sysdate, 'yyyy'), to_char (sysdate, 'mm'), to_char (sysdate, 'dd') FROM dual;

select to_char(trunc(sysdate,'MONTH'),'yyyy-mm-dd') First_DayOfMonth       ,to_char(last_day(trunc(sysdate,'MONTH')),'yyyy-mm-dd') Last_DayOfMonth from dual

SELECT ADD_MONTHS(TO_DATE(LAST_DAY(SYSDATE) + 1), -1) FROM DUAL;SELECT ADD_MONTHS(TO_DATE(LAST_DAY(SYSDATE) ), -1) FROM DUAL;

Appendix: oracle date calculation:

-- Oracle trunc () function usage ********************/ 1. select trunc (sysdate) from dual -- 2011-3-18 today's date is 2011-3-18 2. select trunc (sysdate, 'mm') from dual -- 2011-3-1 returns the first day of the month. 3. select trunc (sysdate, 'yy') from dual -- 2011-1-1 return the first day of the current year 4. select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day 5. select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year 6. select trunc (sysdate, 'D') from dual -- 2 011-3-13 (Sunday) returns the first day of the current week 7. select trunc (sysdate, 'hh') from dual -- 14:00:00 the current time is. select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 TRUNC () the function does not have second precision ****************** ** // * TRUNC (number, num_digits) Number. Num_digits is used to specify the number to take an integer. The default value of Num_digits is 0. When TRUNC () function is intercepted, No rounding is performed */9. select trunc (123.458) from dual -- 123 10. select trunc (123.458, 0) from dual -- 123 11. select trunc (123.458, 1) from dual -- 123.4 12. select trunc (123.458,-1) from dual -- 120 13. select trunc (123.458,-4) from dual -- 0 14. select trunc (123.458, 4) from dual -- 123.458 15. select trunc (123) from dual -- 123 16. select trunc (123) from dual -- 17. select trunc (123,-1) from dual -- 120