SQL Date Math and the Leap Year

Ying pointed out on my article about finding the last Saturday of the year that adding a year to a DATE datatype works great with syntax like this:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N
---------
27-NOV-07

It works great right up until you hit February, 29:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtoyminterval(1, 'YEAR') from dual;
*
ERROR at line 1:
ORA-01839: date not valid for month specified

So what can we do about this? Well, as far as I can guess there are only two options. First, you could avoid ever using +numtoyminterval(1, 'YEAR') in your code and instead use +numtodsinterval(365, 'DAY') like this:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtodsinterval(365, 'DAY') from dual

TO_DATE('
---------
28-FEB-05

This may cause some confusion as 365 days after January 15, 2004 would be January 14, 2005 and 365 days after January 1, 2004 is actually December 31, 2004, but it should never throw an ORA- error.

UPDATE: Ying commented with this solution which is probably better in most scenarios. By using the add_months function we can step forward a number of months and Oracle will automatically truncate to the last day of the month if the resulting month has fewer days than the starting month.

SQL> SELECT Add_Months(to_date('02/29/2004', 'MM/DD/YYYY'), 12) from dual;

ADD_MONTH
---------
28-FEB-05

The other alternative is to handle the exception programmatically. Either avoid inserting a February 29 or avoid using the +numtoyminterval(1, 'YEAR') only when handling a February 29.

I’d say option 1 is the better choice, but both have their drawbacks.

This is the same error you get if you attempt to add a month to, say January 30th since there is never a February 30th.

SQL> select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual;
select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified

I guess this is why most things are good for “30 days” not “1 month”.

Thanks Ying for pointing out this anomaly.

database, oracle, date, to_date, timestamp, time, database administration, database development

Interview with SearchSecurity.com

Earlier this week I was solicited for an interview with Bill Brenner, Senior News Writer for SearcSecurity.com on the topic of Oracle patches and their new Critical Patch Update bulletins.

Bill interviewed several DBAs and got some interesting opinions on Oracle’s patching procedures.

Jon Emmons, an Oracle database consultant and keeper of a blog called Life After Coffee, which focuses on Oracle security and other topics, said he also found the bulletin changes helpful.

“Perhaps the most valuable new feature in the CPU bulletin is the executive summaries,” Emmons said in an email interview. “These bulleted lists give a great high-level summary. At one point or another we’ve all had to explain to our boss why we need to apply these patches and now Oracle has given us the words to do it with.”

The full article, which came out yesterday, highlights mixed opinions on Oracle’s patching procedure and “improved” CPU bulletin.

oracle, patch, database, dba, database administration, rdbms, security, database security

Oracle Security Alerts Available via RSS

Last week I needed the latest security patch for a new install of Oracle Application Server. After spending an inordinate amount of time on Oracle’s site I finally found it. Then I thought “I wonder if there’s an RSS feed for this?”

So I copied the URL out of IE and into a modern browser (Firefox in this case) and sure enough, I got the familiar feed icon in the location bar. I then proceeded to add the link to my RSS aggregator and can now find the updates without hassle!

So, if you’re looking for the latest security alerts from Oracle, here’s the link If you are using a news aggregator or portal which allows RSS you should consider adding the URL to your aggregator so you’ll always have the links to the latest patches handy.

oracle, rdbms, security, database

SQL to find the last Saturday of the year

Satya commented on my post about finding the first or second Monday in a month asking how to find the last Saturday of a year using SQL. This is a good question as I think it is typical of the battles people fight with dates in Oracle.

So here we go… For this example we’ll use sysdate but you can use a date column or a to_date instead.

SQL> select sysdate from dual;

SYSDATE
---------
13-SEP-06

Now we’ll jump forward a year and start working backwards:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N
---------
13-SEP-07

Now that we’re safely into next year we’ll reel it back to January 1st of next year using the trunc function to truncate the date down to the year.

SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') from dual;

TRUNC(SYS
---------
01-JAN-07

To work with the last week of the year we’ll go back 8 days. We need to go back 8 instead of 7 because we’re going to use the next_day function later which only looks after the date it is passed.

SQL> select trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8 from dual;

TRUNC(SYS
---------
24-DEC-06

Now we use the next_day function to look for the next Saturday after the date we’ve got.

SQL> select next_day(trunc(sysdate+numtoyminterval(1, 'YEAR'), 'YEAR') - 8, 'SATURDAY') from dual;

NEXT_DAY(
---------
30-DEC-06

So we figured out the last Saturday of this year by taking today’s date, adding one year, going back to January 1 of that year, stepping back 8 days from then and looking for the next Saturday.

Hope this helps Satya! Thanks for the great question.

oracle, dates, database, sql, dba, dbms, database development, database programming

Database Connection Strings

ConnectionStrings.comHere’s a cool site to look up those pesky connection strings when you’re coding. ConnectionStrings.com has a fairly extensive list of connection strings for all sorts of data sources and programming languages.

It doesn’t have everything, but there’s an email address if you want to submit more connection strings to the site. Why didn’t I think of this!

Thanks to Tom for sending this to me.

database, dba, database administration, database development, development, oracle, mysql, sqlserver, odbc, access, sql server