Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Leap year, last day of the month the easy way?

leave a comment »


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.

Advertisements

Written by maclochlainn

May 29, 2008 at 5:29 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: