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 OTN 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 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.
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).
Almost all programming languages have mechanisms for declaring the TYPE of data we are dealing with. The data type defines
- the possible values for that type,
- the operations that can be done on that type, and
- the way the values of that type are 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.
The DATE and TIMESTAMP in Oracle
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).
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. 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 either the SYSTEM ENVIRONMENT VARIABLES or at 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: 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’;
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 demonstrate the various settings. I’ll create a table with a single DATE column, insert a single row, then repeatedly select that row after altering the session value of NLS_DATE_FORMAT.
SQL> create table mytest (birthdate date); Table created. SQL> insert into mytest values (sysdate); 1 row created. SQL> select * from mytest; BIRTHDATE --------- 06-APR-11 1 row selected. SQL> alter session set nls_date_format='yyyy/mm/dd'; Session altered. SQL> select * from mytest; BIRTHDATE ---------- 2011/04/06 1 row selected. SQL> alter session set nls_date_format='dd/mm/yy hh:mi:ss'; Session altered. SQL> select * from mytest; BIRTHDATE ----------------- 06/04/11 08:43:48 1 row selected. SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; Session altered. SQL> select * from mytest; BIRTHDATE ------------------- 06/04/2011 20:43:48 1 row selected. SQL>
The 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 bday1, 2 to_char(birthdate,'yyyy/mm/dd') bday2, 3 to_char(birthdate,'dd/mm/yy hh:mi:ss') bday3, 4 to_char(birthdate,'dd/mm/yyyy hh24:mi:ss') bday4 5 from mytest; BDAY1 BDAY2 BDAY3 BDAY4 --------- ---------- ----------------- ------------------- 06-APR-11 2011/04/06 06/04/11 08:43:48 06/04/2011 20:43:48 1 row selected.
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 of the session. SQL> select to_char(birthdate,'yyyy/mm/dd hh24:mi:ss') bd1, 2 to_char(birthdate,'D') bd2 3 from mytest; BD1 B ------------------- - 2011/04/06 20:43:48 4 1 row selected. SQL> -- DAY returns Name of day. Is case sensitive SQL> select to_char(birthdate,'DAY') bd2, 2 to_char(birthdate,'Day') bd3 3 from mytest; BD2 BD3 --------- --------- WEDNESDAY Wednesday 1 row selected. SQL> -- DDD returns Day of year (1-366) SQL> select to_char(birthdate,'DDD') bd2 2 from mytest; BD2 --- 096 1 row selected. SQL> -- DY Abbreviated name of day, case sensitive SQL> select to_char(birthdate,'DY') bd2, 2 to_char(birthdate,'Dy') bd3 3 from mytest; BD2 BD3 --- --- WED Wed 1 row selected. SQL> -- IW Week of year (1-52 or 1-53) based on the ISO standard SQL> select to_char(birthdate,'IW') bd2 2 from mytest; BD -- 14 1 row selected. SQL> -- RM Roman numeral month (I-XII; January = I) SQL> select to_char(birthdate,'RM') bd2 2 from mytest; BD2 ---- IV 1 row selected. SQL> -- YEAR Year, spelled out, case sensitive SQL> select to_char(birthdate,'YEAR') bd2, 2 to_char(birthdate,'Year') bd2 3 from mytest; BD2 BD2 ------------------------------------------ ------------------------------------------ TWENTY ELEVEN Twenty Eleven 1 row selected. SQL> -- we can even insert text of our choosing: SQL> select to_char(birthdate,'"I was born in " YEAR') bd2 2 from mytest; BD2 --------------------------------------------------------- I was born in TWENTY ELEVEN 1 row selected. SQL>
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')); 1 row created. SQL> insert into mytest values (to_date('01-JAN-1953','dd-MON-yyyy')); 1 row created. SQL> insert into mytest values (to_date('05/04/63 13:23:47','mm/dd/yy hh24:mi:ss')); 1 row created. SQL> select to_char(birthdate,'dd-Mon-yyyy hh24:mi:ss') 2 from mytest; TO_CHAR(BIRTHDATE,'D -------------------- 06-May-2011 00:00:00 01-Jan-1953 00:00:00 04-May-2063 13:23:47 3 rows selected.
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. Film at eleven.