Leap year, last day of the month the easy way?
You could write an ugly SQL statement full of CASE statements to derive the leap year moving forward or backward in time. You could likewise write an uglier statement that allows for moving back from a 31 day month to a 30 day month or forward from a 30 day month to a 31 day month.
However, you can use the add_months(date_in,number_of_months), like:
Leap year back to a non-leap year (system clock set to 29-FEB-2008):
SQL\> SELECT add_months(SYSDATE,-12) FROM dual; |
From the last day of May to the last day of April (system clock set to 31-MAY-2008):
SQL\> SELECT add_months(SYSDATE,-1) FROM dual; |
From the last day of April to the last day of May (system clock set to 30-APR-2008):
SQL\> SELECT add_months(SYSDATE,1) FROM dual; |
You can read more here in the Oracle SQL Reference.
Subscribe via RSS