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;


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

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


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;


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;


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;


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

Oracle Chooses XServe RAID

Apple's Xserve RAIDIt looks like Oracle has decideded to adopt Apple’s XServe RAID as a low-cost storage solution

Based on our own experience with Apple technology, Xserve RAID is a great match for applications running Oracle.

With the appetite of one to two petabytes annually Oracle is of course looking to control costs and the SATA-based Xserve RAID combines value, capacity, performance and availability. The current top configuration offers 7000GB on dual RAID controllers with redundant power and cooling for a mere $12999. Sure that’s the price of a small car, but it works out to only $1.86/GB for some serous industry-level storage.

Thanks to Zach for sending this on to me.

apple, oracle, xserve, raid, xserve raid, storage, disk, hard drive

This Week in Tech Podcast – Feed your Inner Geek!

TWiTThere are a lot of dry monotone technology podcasts out there, but Leo Laporte’s TWiT podcast isn’t one of them. Every week Laporte brings together a cast of industry leaders to discuss exciting technology topics ranging from the latest offerings from Microsoft to what’s new on RFID hacking.

Laporte, along with regulars Patrick Norton and John C. Dvorak are joined by other technology professionals for this roundtable discussion. Together they run through a dozen or so top technology each week. Typically these podcasts are a bit over an hour, but if you have a long drive like I do, it’s an hour well spent.

If you work in or are studying technology, especially information technology, you should check out the TWIT podcast. Get the MP3 or AAC version on iTunes!

technology, tech, podcast, mp3

10 Writing Tips

Alexander over at PositiveSharing.com has posted his Top 10 tips for productive, creative, fun writing.

So how’d [write a whole book in twenty days]? Well the answer is obvious isn’t it? Clear goals, hard work, perseverance, sticking to it, eliminating distractions and writing no matter what, right?


Wrong, wrong, wrong. I tried that. Didn’t work. So I tried the exact opposite and that worked.

Like most of Alexander’s posts, this list is both highly applicable and refreshingly unconventional. Some of these things I find I’ve already been doing and others I’ll have to remember to try.

While my publisher would probably take issue with #9 (No deadlines or goals) I’m sure these tips will find their way into how I blog and work on my books.

writing, blogging