Getting Started With Oracle: Working With Dates

Contrary to popular belief

You will not burst into eternal flames if you’re a SQL Server guy or gal, and you happen to be within 100 feet of an Oracle database. You might feel lost and confused for a while, but you probably felt the same way when you opened up SSMS for the first time.

I’ve been trying to expand my horizons a bit, so I followed some reasonably simple download instructions to get my feet wet in the ocean of gold hundred dollar bills and lobster/veal hybrids that is Oracle. The next thing I needed was some sample data, so I grabbed the HR stuff. I don’t need anything too complicated yet.

Is that Oracle handles multi-tenancy a bit differently than SQL Server does, at least up until 12c and the introduction of pluggable databases. Where in SQL Server you create databases, in Oracle you create users and schema. Creating an Oracle database happens when you install the software. It’s weird at first. Like when you move into a new apartment and keep reaching to the wrong side to switch the bathroom light on.

If you want to see what data exists for other users, you expand the Other Users node to view tables, indexes, and views, etc.

Sympathy for the Larry

Sympathy for the Larry

Why start with dates?

No reason! We’ll talk about numbers and string later.

Another minor quirk when working with Oracle is that you can’t do what you do in SQL Server:

In Oracle, you have to reference the DUAL table; it’s just one column called DUMMY and one row with an X in it.

Retrieving system dates goes like this:

SYSDATE just gives you the date, SYSTIMESTAMP gives you date, time and timezone offset.

Deep breaths!

Deep breaths!

The first thing you’ll notice is that Oracle presents dates differently — DD-MON-YY — which is something that, curiously, I have seen pop up as a requirement for people to do with SQL Server. Because SQL Server is a great presentation layer.

(It’s really not.)

Format is everything

If you do want to make your dates look a little prettier, you have to do a little wrangling with the TO_CHAR function. It allows you to specify string formatting of date and time data.

Modern Art.

Modern Art.

Which naturally bring us to comparing dates

You didn’t look at all that stuff and think you’d get off easy, did you? SQL Server jumps through a lot of hoops to let you pass in date values as predicates. Oracle, not so much. You have to do some hand holding.

Below are some queries where I tried to pass in date literals with varying degrees of success. Well, okay, the degrees of success were pretty binary. Not a lot of grey area when queries error out, huh?

The main point to take away here is that Oracle is much pickier about this than SQL Server is. It’s totally fine to pass in ‘2000-01-01’ and a lot of different variations in formatting and delimiting and still get valid results back. Whether that’s good or bad is up to you. I like the ease and flexibility of SQL Server, but I also like that there’s far less ambiguity in Oracle.

I’m learning at the same time some of you are

So if there are any mistakes, misgivings, trick shots, or better ways to do things than I have written, feel free to leave comments.

Join me next time where I’ll look at some date math examples!

Thanks for reading!

Brent says: wow, I’m surprised it doesn’t take yyyy-mm-dd without the “DATE” prefix. I’ve been trying to break my American habits and use the yyyy-mm-dd format in my demos to work better with a worldwide audience, and now I feel like I’m behind again.

Previous Post
The Top 4 Job Tasks DBAs Forget
Next Post
Getting Started With Oracle: Date Math

7 Comments. Leave new

  • “I like the ease and flexibility of SQL Server, but I also like that there’s far less ambiguity in Oracle.” That’s a great observation. I had similar thoughts many years ago as a dev working on an app that supported both vendors. I seem to remember using the Oracle function TO_DATE() a lot…

  • Date math is easier in Oracle than in any other database in my experience, in large part because TRUNC() does everything by magic. Need the first day of the week (Sunday) TRUNC(CURRENT_DATE,’D’). First day of the month? TRUNC(CURRENT_DATE,’MON’). Quarter: TRUNC(CURRENT_DATE,’Q’). Year: TRUNC(CURRENT_DATE,’YYYY’). ISO Year: TRUNC(CURRENT_DATE,’IYYY’).

    Combined with the fact that simply adding and subtracting integers with dates will simply add and subtract days makes creating date expressions extremely simple.

    • Yeah, TRUNC does a lot of cool stuff. But I still think DATEADD/DATEDIFF are really powerful tools.

  • ” It’s totally fine to pass in ‘2000-01-01’ and a lot of different variations in formatting and delimiting and still get valid results back.”

    But what about ‘2000-03-09’?
    Results, yes.
    Valid?
    Is that March 9 or September 3?

    • Like most platforms, that’s dictated by local region/language settings.

      In Oracle, it’s set via ALTER SESSION SET NLS_DATE_FORMAT at the session level. In SQL Server, you can SET DATEFORMAT at the session level.

  • Your statement ‘Creating an Oracle database happens when you install the software’ is not the whole truth. When you install the software you have the option to install a database, and after installing the software you can create additional databases. But it’s more complicated than SQL Server. You can use the ‘create database’ statement but it’s easier to use the DataBase Creation Assistant (DBCA) to create new databases.

  • I was unable to install HR database after download.

Menu
{"cart_token":"","hash":"","cart_data":""}