DB2 vs. Oracle – handling dates

Posted by

So as I may have mentioned before, I’m learning Oracle. I’ve always been a DB2 DBA, having started out as strictly a Physical/Systems DBA. Thus SQL is a skill I’ve built very slowly, over time. I thought the hardest test for DB2 certification was the ‘Family Fundamentals’ one, because it focused so heavily on SQL and other things I didn’t do day-in and day-out at the time.

Since then, I’ve moved into a more mixed role and do a lot more SQL including stored procedures and such. Right now, I’m working on learning SQLPlus so I’m ready for an Oracle Admin 1 class in May. As I poke around, there are some things that seem nice, others that are just different, and a lot the same too.

What struck me today is how much more easily DB2 handles simple date/time manipulation. I suppose part of this may be familiarity, and I see how Oracle’s methods are more in line with other programmatic approaches. But I thought I’d work through a couple of examples to illustrate the differences – both to clarify it in my own head and to help readers who might be struggling with the same transition.

Ok, so I’m picking some examples from the standard schemas in Oracle, and will then talk through what they would be given a similar data structure in DB2.

Example #1:

Finding rows with a ‘birthday’ from two days in the past to 7 days in the future, regardless of year

Here’s the essence of the answer from one of the problems in my Oracle SQL Book:

  1  select cust_first_name, cust_last_name
  2  from oe.customers
  3  where to_number(to_char(date_of_birth, 'DD')) - to_number(to_char(sysdate,'DD')) between -2 and 7
  4  and to_char(date_of_birth,'MON') = to_char(sysdate,'MON')
  5  order by to_char(date_of_birth, 'MMDD')

Now, I don’t even like that solution much because it wouldn’t work if you were near the end or beginning of the month. But going for the exact same functionality in DB2 (different table, but you get the point):

select users_id
from users
where month(REGISTRATION) = month(current timestamp)
    and day(current_timestamp) - day(registration) between -2 and 7
order by Month(registration), day(registration)
with ur

It just seems easier to read the DB2 way.

Example #2:

Find what the date was 36 hours ago

This one is probably just a matter of preference. The Oracle way:

SQL> select sysdate-36/24 from dual
  2  ;


The DB2 way:

/db_logs> db2 "select date(current timestamp - 36 hours) from sysibm.sysdummy1"


  1 record(s) selected.

Example #3:

Find the day of the week one month ago


  1  select to_char(add_months(sysdate, -1), 'DAY') weekday from dual
SQL> /



select dayname(current timestamp - 1 month) weekday from sysibm.sysdummy1


  1 record(s) selected.

Perhaps I’m the only one, but using an “add months” function to subtract them is a bit annoying – especially when db2 doesn’t require me to use a function at all for that part – with Oracle, I’m using two functions, but with DB2, I only need one.

Example #4:

Find the exact time 450 seconds from now:


SQL> l
  1  select to_char(sysdate+450/1440,'MM/DD/YYYY HH24:MI:SS') time from dual
SQL> /

04/15/2011 23:53:43


select current timestamp +450 seconds time from sysibm.sysdummy1


  1 record(s) selected.

Perhaps one just gets used to knowing how many minutes in a day and all of that. I do think you generally have to do fewer functions to deal with dates in DB2. I don’t think DB2 will spell out the year like Oracle will, and Oracle can’t handle microseconds – either of which might bug you in some situations, but rarely in Commerce databases.

Sometime I’ll figure out if Oracle backup/recovery is really quite as bad as it looks to a newbie and write an article comparing and contrasting those.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.


  1. For the first example I would do:

    select cust_first_name, cust_last_name
    from oe.customers
    where date_of_birth – sysdate between -2 and 7
    and extract (month from date_of_birth) = extract (month from sysdate)
    order by date_of_birth –<< It is already order by date, Jan always comes before Feb, except you want it the other way round, you put 'desc'

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.