Taking a breather from my exploration of TNS connection issues, I’d like to take a quick romp through the use (and mis-use) of date formatting. There are no deep mysteries revealed here, and everything is quite well documented. But judging from traffic on popular Oracle forums, it appears this is an area that leaves a lot of confusion. I will try to address those points of confusion directly, and provide examples that will, hopefully, clarify the documentation by connecting some of the dots that people seem to miss.
A very frequent question on OTN is some variation on this:
I have a column, birthdate, that looks like 23-Mar-2011. How do I get Oracle to store the date in the format 2011/03/23?
And the short answer is, “you don’t.”
Invariably, when people ask this type of question what they really want is to be able to display the date in their chosen format. They often mistakenly believe they have to store it in a given format in order display it in that format.. They don’t understand that (1) a column defined as a DATE stores the data in Oracle’s internal format and (2) that column can be displayed in any number of formats in spite of (actually, because of) that internal format.
The internal format is has been documented since at least Oracle 9i, in the Call Interface Programmer’s Guide. The 12c version of that document is found here, with the specific description of the DATE format internal storage located here. The rest of this article will attemp to bring some clarity and understanding to the practical aspects of dealing with dates in an Oracle database.
Using proper data type
In order to understand “you don’t” store the date in whatever-format-you-say, you have to understand the concept of DATA TYPE. (I’ll save the rant for a separate post).
Anything we see in the written (or printed, or displayed-on-a-screen) is nothing but a set of symbols – “characters”. But dates (and numbers) are not characters, they are concepts. When we write a date or a number on paper (or display it on a screen) we not are writing the date itself, but a character representation of a date.
Almost all programming languages have mechanisms for declaring the TYPE of data we are dealing with. The data type defines
- the possible values for data,
- the operations that can be done on the data
- the way the data of that type is stored.
It occurs to me that the definition of a data “type” sounds similar to the definition of an object in object oriented programming – even though data typing has been around much longer that OOP. Oracle has a rich set of data types, all documented in the SQL Reference Manual.
Think about the power this simple concept brings to programming. If I treat everything as a character string, there is nothing to keep me from saying my birthday is “fred”. Or that your salary is “fubar”. There is no way to give you a raise by computing “emp_sal *5”, because internally there is no functional difference between “50000” and “get a pink slip”. Or if I declare the column DAY_OF_BIRTH as a NUMBER, there is nothing to keep me from saying my birthday is 1, or 999999999999. Or entering numbers that look like dates, but certainly don’t get treated like dates, and have no integrity as dates.
In my first drafts of this post I spent quite a bit of time at this point making the case for using the proper data type (DATE) for dates. For the sake of brevity I’ll defer that discussion to another post. For now let’s just assume that your date data is properly typed as DATE, and discuss how to deal with that to get the results you want.
Oracle’s DATE and TIMESTAMP Types
When a column is declared as a DATE or TIMESTAMP, Oracle stores that data in an internal format that is very efficient for dealing with a very large range of dates and times. Dan Morgan has a more through explanation of the details of this internal format at http://psoug.org/reference/datatypes.html. What’s important for us to know is that 1) the data is stored in Oracle’s format, not ours and 2) it includes both date and time. For DATE the time component is down to the second, but not fractions of seconds. TIMESTAMP can store date and time down to billionths of seconds (9 decimal places). TIMESTAMP also has some variants (TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE), but we can ignore these for the sake of the current discussion.
Please note that through the remainder of this article everything said about DATE applies equally to the TIMESTAMP types.
NLS_DATE_FORMAT
When people say Oracle isn’t storing the date in the format they wanted, what is really happening is Oracle is not presenting the date in the character string format they expected or wanted.
When a data element of type DATE is selected, it must be converted from its internal, binary format, to a string of characters for human consumption. The conversion of data from one type to another is known as known a “conversion”, “type casting” or “coercion”. In Oracle the conversion between dates and character strings is controlled by the NLS_DATE_FORMAT model. The NLS_DATE_FORMAT can be set in any of several different locations, each with its own scope of influence.
The weakest setting of NLS_DATE_FORMAT is in the database initialization parameters. Ultimately, all formatting is in the hands of the client process, but lacking any other sources, the client will get its value of NLS_DATE_FORMAT from the database. The reason I say this is the weakest setting is because it is overridden by several other settings, and does not override any settings itself. Setting NLS_DATE_FORMAT in the initialization parameters will be good only until it is overridden by setting it somewhere else, like . . .
Setting it as an OS environment variable on the client machine. In unix that is “export NLS_DATE_FORMAT=somevalue”. In Windows it can be set at either the system environment variables or the command prompt in a command session. I’d think it could also be set in the registry (see http://support.microsoft.com/kb/104011) but I’ve not been able reproduce it. Between setting it at the system environment variables and at the command prompt, the command prompt setting will take precedence.
I won’t spend any time explaining or demonstrating how to set it at the OS because this is, in turn, overridden by . . .
Setting it at the oracle client session level with the statement ‘ALTER SESSION SET NLS_DATE_FORMAT=<somesetting>’. That, in turn, is overridden by . . .
Use of the ‘to_date‘ and ‘to_char‘ functions at the individual sql statement.
Considering the long chain of overriding settings, you should never depend on a setting outside of your own immediate control. That means you will always do one of the following:
1) ALTER SESSION SET NLS_DATE_FORMAT=’whatever format model you want’;
or
2) Proper use of TO_CHAR and TO_DATE at the individual sql statement
I prefer the second, so there is never any ambiguity or question when looking at an individual SQL statement.
Just to drive home the point, let’s do a little demonstration of the various settings. First, create a table with two columns, one a proper DATE and the other a character data type (VARCHAR2), and insert a single row with the same data in both columns. Note that I am inserting sysdate into both columns. Since sysdate returns the current system date-time as a DATE, the insertion into BIRTHDATE_CHR (a varchar2) will cause an implict conversion to a character string.
SQL> create table mytest ( 2 birthdate_dte date, 3 birthdate_chr varchar2(30) 4 ) 5 ; Table created. SQL> insert into mytest values (sysdate, sysdate); 1 row created. SQL> commit; Commit complete. SQL>
Fig. 1
Nex, repeatedly select that row after altering the session value of NLS_DATE_FORMAT. At each point, notice the difference in the returned value of BIRTHDATE_DTE vs. BIRTHDATE_CHR.
SQL> select * from mytest; BIRTHDATE BIRTHDATE_CHR --------- ------------------------------ 06-FEB-16 06-FEB-16 1 row selected. SQL> alter session set nls_date_format='yyyy/mm/dd'; Session altered. SQL> select * from mytest; BIRTHDATE_ BIRTHDATE_CHR ---------- ------------------------------ 2016/02/06 06-FEB-16 1 row selected. SQL> alter session set nls_date_format='dd/mm/yy hh:mi:ss'; Session altered. SQL> select * from mytest; BIRTHDATE_DTE BIRTHDATE_CHR ----------------- ------------------------------ 06/02/16 09:51:31 06-FEB-16 1 row selected. SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; Session altered. SQL> select * from mytest; BIRTHDATE_DTE BIRTHDATE_CHR ------------------- ------------------------------ 06/02/2016 09:51:31 06-FEB-16 1 row selected. SQL>
Fig. 2
Oracle datetime functions
There is a second method of controlling the format of a selected DATE data element. Even a session level setting of NLS_DATE_FORMAT can be overridden by use of the datetime functions TO_CHAR and TO_DATE at the individual sql statement. This explicit control cannot be overridden by anything else. That is why it is my preferred method, and the one I press on anyone I am training.
The TO_DATE function takes a character string and a description of that string to return a DATE. It is most commonly used when inserting or updating a DATE. More on that later.
The second datetime function is TO_CHAR. It takes a DATE and a format string to return a character string representation of the date, formatted according to the format string. This format string is formed exactly the same as when setting NLS_DATE_FORMAT.
At this point some people may still be harboring the notion that what I did with ALTER SESSION SET NLS_DATE_FORMAT was somehow altering the data in the table (in spite of what ALTER SESSION really means) but this next example should make it crystal clear that we are simply formatting the data for presentation.
SQL> select birthdate_dte bday1, 2 to_char(birthdate_dte,'yyyy/mmdd') bday2, 3 to_char(birthdate_dte,'dd/mm/yy hh:mi:ss') bday3, 4 to_char(birthdate_dte,'dd/mm/yyyy hh24:mi:ss') bday4 5 from mytest; BDAY1 BDAY2 BDAY3 BDAY4 --------- --------- ----------------- ------------------- 06-FEB-16 2016/0206 06/02/16 09:51:31 06/02/2016 09:51:31 1 row selected. SQL>
Fig. 3
NLS Datetime Format Elements
The string of characters defining the format we want for the character representation of the date (the “format model”) is made up of “format elements”. They are fully documented in the SQL Reference Manual, under “Format Models“. Just for fun, let’s play around with some of the lesser used elements.
SQL> -- D returns Day of week (1-7). Depends on the NLS territory SQL> select to_char(birthdate_dte,'yyyy/mm/dd hh24:mi:ss') bd1, 2 to_char(birthdate_dte,'D') bd2 3 from mytest; BD1 B ------------------- - 2016/02/06 09:51:31 7 1 row selected. SQL> -- DAY returns Name of day. Is case sensitive SQL> select to_char(birthdate_dte,'DAY') bd2, 2 to_char(birthdate_dte,'Day') bd3 3 from mytest; BD2 BD3 --------- --------- SATURDAY Saturday 1 row selected. SQL> -- DDD returns Day of year (1-366) SQL> select to_char(birthdate_dte,'DDD') bd2 2 from mytest; BD2 --- 037 1 row selected. SQL> -- DY Abbreviated name of day, case sensitive SQL> select to_char(birthdate_dte,'DY') bd2, 2 to_char(birthdate_dte,'Dy') bd3 3 from mytest; BD2 BD3 --- --- SAT Sat 1 row selected. SQL> -- IW Week of year (1-52 or 1-53) based on the ISO standard SQL> select to_char(birthdate_dte,'IW') bd2 2 from mytest; BD -- 05 1 row selected. SQL> -- RM Roman numeral month (I-XII; January = I) SQL> select to_char(birthdate_dte,'RM') bd2 2 from mytest; BD2 ---- II 1 row selected. SQL> -- YEAR Year, spelled out, case sensitive SQL> select to_char(birthdate_dte,'YEAR') bd2, 2 to_char(birthdate_dte,'Year') bd2 3 from mytest; BD2 ------------------------------------------ BD2 ------------------------------------------ TWENTY SIXTEEN Twenty Sixteen 1 row selected. SQL> -- we can even insert text of our choosing: SQL> select to_char(birthdate_dte,'"I was born in " YEAR') bd2 2 from mytest; BD2 --------------------------------------------------------- I was born in TWENTY SIXTEEN 1 row selected. SQL>
Fig. 4
Inserting data with to_date
Up to this point we have only discussed selecting data. Everything we’ve talked about applies in reverse with the TO_DATE function. This function takes a character string and a format model describing that character string, and returns an internal DATE format. It is used for INSERTing and UPDATEing data as it is stored in a table. From what you’ve seen to this point, a quick example should make it clear.
SQL> truncate table mytest;
Table truncated.
SQL> insert into mytest values (to_date('20110506','yyyymmdd'),null);
1 row created.
SQL> insert into mytest values (to_date('01-JAN-1953','dd-MON-yyyy'),null);
1 row created.
SQL> insert into mytest values (to_date('05/04/63 13:23:47','mm/dd/yy hh24:mi:ss'),null);
1 row created.
SQL> select to_char(birthdate_dte,'dd-Mon-yyyy hh24:mi:ss')
2 from mytest;
TO_CHAR(BIRTHDATE_DT
--------------------
06-May-2011 00:00:00
01-Jan-1953 00:00:00
04-May-2063 13:23:47
3 rows selected.
SQL>
Fig. 5
Conclusion
Hopefully by this point you will see that storing data as a date and presenting that data in a result set are two different things. A couple of final points need to be made.
First, the to_char function is used to convert from a date to a character string. It makes no sense to give it a character string as an input argument. Conversely, the to_date function converts a character string to a date. It makes no sense to give it a date (datatype) as an arguement, yet we often see something like this:
sql> select to_date(sysdate) from dual;
Second, when designing a table, one should always use the appropriate data type for the type of data being stored. Dates and times are stored in datetime columns, numbers are stored in numeric columns. I’ll write more on that later.
Of course, all I’ve shown here is to drive home the point about display vs. storage. We still need to discuss usage in comparisons — in the WHERE clause of a SQL statement. That will be the subject of my next article.
— Added 5 Nov 2016 —
Thanks to my Oracle Technology Network friend “rp0428” for pointing out that I had omitted links to Oracle documentation that actually describes the internal storage format of DATE data types. He pointed out that this information has been documented since at least Oracle 9i, in the Call Interface Programmer’s Guide. The 12c version of that document is found here, with the specific description of the DATE format internal storage located here. The OTN member that goes by the name “BlueShadow” has also written a good description of the internal storage format. The OTN posting that provides a link and further discussion is located here.
Ed Stevens, DBA 