Getting Started With Oracle: Working With Dates

Last Updated 8 years ago

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.

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:

SELECT GETDATE()

1	SELECT GETDATE()

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

SELECT * FROM DUAL;

1	SELECT * FROM DUAL;

Retrieving system dates goes like this:

SELECT SYSDATE, SYSTIMESTAMP
FROM DUAL;

1 2	SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

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

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.

SELECT 
TO_CHAR( SYSDATE, 'YYYY-MM-DD HH:MI:SS') AS "SYSDATE Formatted" ,
TO_CHAR( SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF') AS "We need milliseconds",
TO_CHAR( CAST(SYSTIMESTAMP AS TIMESTAMP(3) ), 'YYYY-MM-DD HH:MI:SS.FF') "Maybe only three milliseconds",
TO_CHAR( CAST(SYSTIMESTAMP AS TIMESTAMP(3) ), 'YYYY-MM-DD HH24:MI:SS.FF') "And 24 hour time"
FROM DUAL;

SELECT

TO_CHAR( SYSDATE, 'YYYY-MM-DD HH:MI:SS') AS "SYSDATE Formatted" ,

TO_CHAR( SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF') AS "We need milliseconds",

TO_CHAR( CAST(SYSTIMESTAMP AS TIMESTAMP(3) ), 'YYYY-MM-DD HH:MI:SS.FF') "Maybe only three milliseconds",

TO_CHAR( CAST(SYSTIMESTAMP AS TIMESTAMP(3) ), 'YYYY-MM-DD HH24:MI:SS.FF') "And 24 hour time"

FROM DUAL;

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?

--NO, hated this all day long.
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= '2000-01-01'

--YES, date passed in using standard Oracle DD-MON-YY format
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= '01-JAN-00';

--YES, close enough to above, I suppose.
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= '01-JAN-2000';

--NO, MM-DD? DD-MM? How is it supposed to know?
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= '01-01-2000';

--YES, prefixing the string with DATE brings an uneasy truce.
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= DATE '2000-01-01';

--YES, explicitly casting the string to a date with format works.
SELECT *
FROM HR.EMPLOYEES HR
WHERE HR.HIRE_DATE >= TO_DATE('2000-01-01', 'YYYY-MM-DD');

--NO, hated this all day long.

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= '2000-01-01'

--YES, date passed in using standard Oracle DD-MON-YY format

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= '01-JAN-00';

--YES, close enough to above, I suppose.

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= '01-JAN-2000';

--NO, MM-DD? DD-MM? How is it supposed to know?

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= '01-01-2000';

--YES, prefixing the string with DATE brings an uneasy truce.

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= DATE '2000-01-01';

--YES, explicitly casting the string to a date with format works.

SELECT *

FROM HR.EMPLOYEES HR

WHERE HR.HIRE_DATE >= TO_DATE('2000-01-01', 'YYYY-MM-DD');

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.

The Top 4 Job Tasks DBAs Forget

Getting Started With Oracle: Date Math

7 Comments. Leave new

Dave Mason
February 15, 2016 8:35 am

“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…

Reply
CarlosT
February 15, 2016 7:56 pm

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.

Reply
- Erik Darling
  February 16, 2016 4:17 pm
  
  Yeah, TRUNC does a lot of cool stuff. But I still think DATEADD/DATEDIFF are really powerful tools.
  
  Reply
Mal Parnell
February 16, 2016 4:09 pm

” 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?

Reply
- Erik Darling
  February 16, 2016 4:15 pm
  
  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.
  
  Reply
Kees Schouten
February 17, 2016 4:52 am

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.

Reply
nikolz
November 26, 2016 4:48 pm

I was unable to install HR database after download.

Reply