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

Oracle WTF?

Web surfing today I stumbled upon Ora-WTF.blogspot.com. This will be one to follow.

Now most of the world won’t understand why logging users clear-text passwords in a table is a bad idea, or that your error handling should handle errors, not cause them, but for those of us who get some perverse pleasure from disaster prone, elaborate solutions to everyday problems this is a great site!

I am amused. This blog has just the right attitude for my current mood (spread too thin, working on too many disparate projects at once, and jealous of my student worker who gets to work on one thing at a time, at least at work.)

oracle, weblog, blog, wtf, database administration, database programming

Evaluating Null in Oracle

In a previous article Oracle conditions and how they handle NULL I give some examples on how NULL is evaluated in Oracle. After talking with Scott, a student who is doing some database work with me this semester I believe I may have a better way of explaining the initially cryptic evaluation of NULL.

NULL in Oracle is essentially considered a non-answer. For example let’s consider this yes/no question:

Are you currently living in the USA?

There are two obvious answers to this question, Yes and No, but we need to be prepared for one more circumstance, a non-answer.

So if you ask me this question and I don’t answer it can you say “Jon is currently living in the USA”? No. Can you say “Jon is not currently living in the USA”? No! We can’t compare against something we don’t know so any comparisons against NULL are treated as false.

To handle these circumstances in Oracle we must use IS NULL and IS NOT NULL to detect these non-answer values. For some examples of this code and more detail on this check out my original article on the topic.

database, database administration, database programming, dba, oracle

EditPlus for SQL Editing and More

EditPlus in actionWhile there are many text editors out there offering a broad set of features my favorite right now is EditPlus.

EditPlus is a Windows shareware application designed for text, HTML, Java, PHP, etc. It’s very thin requiring little (nearly no) load time and has many great features such as:

  • Syntax hilighting for many languages
  • EditPlus on Windows right-click
  • FTP and SFTP integration
  • Templates
  • Line Numbering
  • Column Selection
  • Optional Spell Checker

The list of features is long and every revision brings more. Check out the Features page for more.

I’ve been using the Oracle 9iR2 syntax file from the user files section of EditPlus.com and it seems to pick up all the SQL and PL/SQL syntax I use, although many other syntax files exist and you could always make your own.

As mentioned above, EditPlus is shareware. A single user license is only $30, and with discounts for buying in bulk there is no reason not to pay, but for now, download it, try it, and see why you can’t live without it.

Thanks to Zach for showing this to me a couple years ago.

text, text editing, editing, editor, edit, sql, plsql, pl/sql, sftp, ftp, notepad, wordpad, oracle, unix, php, perl, programming, database programming, dba, database administration, systems administration, c, c++, java, javascript, css, html

Blogs.Oracle.com

OracleOracle has taken an interesting step, and I hope others will follow.

Blogs.Oracle.com seems to have been created as an Oracle blogroll! Why is this important? Well, for one thing it means Oracle is encouraging their employees to blog, but additionally they are also linking to non-employee blogs.

Here’s the introduction from Blogs.Oracle.com:

Welcome to the Oracle blogging community, where Oracle executives, employees, and non-employees alike exchange views about best practices for using Oracle and industry-standard technologies. This continuous feedback loop helps Oracle stay in touch with the needs of the overall community, so keep those comments coming!

This not only validates what others have been saying regarding the value of blogging in the corporate environment, but also recognizes the role that non-employees play in the big picture.

Check it out at Blogs.Oracle.com. Right now there are a couple dozen employee blogs and fourty-something non-employee blogs. Hopefully they’ll stay on top of adding new folks as they come along.

blogging, information technology, internet, technology, web, web 2.0, web office, blog, database, database administration, database programming, dba, pl/sql, plsql, sql, oracle