QuickTip: Oracle Date Format gotchas

Oracle has Date, Timestamp types for columns that store Date information. We constantly use To_date and To_Char functions to be able translate between Date and Text formats. Both functions work with various formats we show date and time in real life.

For e.g., to show date in MM/DD/YYYY format, you will use,


I’ve used Oracle SYSDATE function here, but it can be any DATE column.

And if you want to include time part also,

SELECT To_Char(date_field, 'MM/DD/YYYY HH24:MI:SS') FROM dual;

Where, HH24 refers to 24 hour clock. HH would show it in 12 hour clock format/

I don’t know if you noticed; there is a gotcha here: Note that Oracle uses MI (instead of usual MM in other languages) in the format string for minutes and SS for seconds.
If you accidentally used MM for minutes, you are in for a big surprise.

You will keep showing the month there, until you realize the minutes are not changing and you see that somehow matches the month, and then it will hit you – mm = Month and mi = Minute !!

By the way, for month we use mm for 2 digit number, mon for the 3 letter code for month (like APR) and month for printing full month name.

Now, what if you want to add some text to the format? For e.g., You want to say, “Today is 28th of April 2016”?

To do this, we may be tempted to try,

SELECT To_Char(date_field, 'Today is ddth of month YYYY') FROM dual;

Nope, that won’t work! When you execute the above SQL, you will get an error:
ORA-01821: date format not recognized

as Oracle considers anything inside the quoted format string to be part of the formatting itself. “Today is” and “of” are not part of the formats allowed.

To avoid this error and format it correctly, you will have to use double quotes (“), like so:

SELECT To_Char(SYSDATE, '"Today is" ddth "of" month YYYY') FROM dual;

There is another gotcha here: Notice th in bold – I didn’t have to quote th, as DDTH (or ddth, as format is case insensitive) is part of Oracle’s format strings for date ordinal number. If I did quote that, then I will get 1th, where as if we leave it to Oracle
(without quoting, it correctly returns 1st.

To do similar thing with time, use:

SELECT To_Char(date_field, 'HH24"h" MI"m" SS"s"') FROM dual;

This will print current time as,
15h 49m 24s

See this post for a complete details of Date formatting in Oracle.


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s