Tuesday, November 26, 2013

TO find OUT previous, CURRENT AND NEXT years FIRST DATE AND last DATE

SELECT TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -TO_NUMBER (TO_CHAR (SYSDATE, 'mm')))), 'YEAR' ) privious_year_first_date,
  TRUNC (last_day (add_months (sysdate,      -to_number (TO_CHAR (sysdate, 'mm')))) ) privious_year_last_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 12   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ), 'YEAR' ) current_year_first_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 12   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ) ) current_year_last_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 24   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ), 'YEAR' ) next_year_first_date,
  TRUNC (last_day (add_months (sysdate, 24   - to_number (TO_CHAR (sysdate, 'mm')) ) ) ) next_year_last_date
FROM DUAL;

1 comment:

  1. Thanks a lot .....this is what i was looking for exactly...you made it look simple...thanks a lot again

    ReplyDelete