Some examples of non-standard sql syntax features. Part Two: Date Formats

Introduction

This article focuses on date formats in Oracle and some features of their processing. The article provides an overview of several standard date formatting masks, explicit and implicit conversion of strings to dates, and additional parameters affecting this process. As in the first part of the article, the discussion of the material is based on examples that demonstrate non-standard formatting capabilities. The Oracle mechanisms involved in the implicit conversion process are discussed in detail. The description of most features is accompanied by links to the relevant sections of the documentation.

Initially, I did not plan on writing an article about dates, but I was going to dwell on the consideration of only one issue on this subject. However, during the work there was a need to highlight the various additional features of Oracle, new examples began to appear. So consideration of one issue has grown into a small article. I hope it turned out not boring, despite not the most interesting topics.

The first part of the article is devoted to the features of the order by operator, the operation of not in and an example of implicit type conversion is here .

')

To_date function and date formats

Few programmers love the formatting theme. For example, on some courses, the topics of formatting dates and regional standards are specifically put on the last hours of the last day of the training, since listeners tedious. The reason is a large number of existing formats with relatively rare use in standard tasks. Most often, masks are used in three functions: to_number , to_char and to_date . In all three cases, the mask is the second optional parameter. And if there are more or less sane number of masks for formatting numbers, there are a lot of masks for formatting dates to obscene, plus suffixes and modifiers .

Of course, the availability of a large number of masks is a positive thing, because it expands the possibilities, for example, to check whether September 13, 2011 is a programmer's day, you can use the 'DDD' mask, which returns the day number in a year:

-- №1 select to_char(to_date('13.09.2011'),'DDD') "Programmers day" from dual; 

Despite the obvious benefit of formatting, I did not plan to include in the second part of the article a review of date formats and examples of the use of exotic masks. Firstly, it is unlikely that it will be interesting to anyone, and secondly, the author is also not a big admirer of complex formatting, since he rarely uses it in life. The only reason for the appearance of this section is some questions that have arisen to readers about the use of the RR format.

Before going directly to the main topic of the section, let's look at a few non-standard examples of working with dates.

Example number 1. Using trimmed templates
Let's start with the standard formatting. Let today's date 09/16/2011, whether the following requests will be executed, and what they will return?

 -- №2 select to_char(sysdate, 'YYYY') from dual; -- №3 select to_date('03', 'DD') from dual; 

Request No. 2 is a typical example of converting a date to a string and casting it to the desired format. The only difference is that instead of the more familiar “DD.MM.YY” or “DD-MON-YYYY” type masks, we used a mask that sets only a year. Request 2 will succeed and return the current year in a four-digit format, i.e. '2011'.

Request number 3 is a bit more interesting, it is a typical example of explicitly converting a string to a date with a reduced format mask, therefore, from the point of view of the syntax, the request is correct and will succeed. A more important issue is the result of its implementation, i.e. What date will it return if only the day is set? Before answering this question, let's remember how Oracle sets the time if it is not explicitly specified:

 -- №4 select to_char(to_date('03.02.2011','DD.MM.YYYY'),'DD.MM.YYYY HH24:MI:SS') from dual; -- №5 select to_char(to_date('03.02.2011 30', 'DD.MM.YYYY MI'),'DD.MM.YYYY HH24:MI:SS') from dual; 

In request No. 4, the time is not indicated; in request No. 5, only the number of minutes is indicated, hours and seconds are omitted. In Oracle, there is a rule according to which, if there is no time component in the date, then the time is automatically set to 00:00:00 (ie, midnight), if only a part of the time elements is specified (as in query No. 5), then the missing the elements are set to 00. Therefore, query number 4 will return the string '03 .02.2011 00:00:00', and query # 5 - '03 .02.2011 00:30:00'.

Let us return to query number 3, is this rule true for dates, i.e. Are the date elements missed during the conversion replaced by 00 or 01? Yes, they are replaced, but not all, more precisely, the values ​​from sysdate (the first day of the current month of the current year) are used for missing date elements. Therefore, query number 3 will use 09 as the month and 2011 as the year, so the result of the query will be September 3, 2011.

Example number 2. Formatting Order
Will the next query be executed, and if so, what date will it return?

 -- №6 select to_date('20092011', 'YYYYDDMM') from dual; 

At first glance, the absence of delimiters in the row with the date may seem a critical factor incompatible with the execution of the query, but the date mask is also set without delimiters and the string to be converted matches the specified pattern. Therefore, query number 6 will succeed and return on 11/20/2009 (the format of the result may differ slightly depending on the session settings). We will discuss the issues related to delimiters in more detail in the following example.

Example number 3. Implicit conversion
Let the default date format be DD.MON.RR, and the date language be Russian, will the following query work:

 -- №7 select months_between('1\-9','01$/08') from dual; 

This query specifies two string parameters that should be converted to dates using implicit conversion. According to the documentation, when using default formats, the string for implicit conversion to a date must match the pattern: [D | DD] separator1 [MM | MON | MONTH] separator2 [R | RR | YY | YYYY] . As separator1 and separator2, you can use most separators and special characters, including spaces, tabs, as well as “and double double quote”. Moreover, if the line contains at least two digits to specify the days, months and years, then separator can generally be omitted. For example:

 -- №8 select to_date('0109') from dual; -- №9 select to_date('0109') from dual; -- №10          DD.MM.RR select to_date('010909') from dual; 

Since both strings specified in query No. 7 correspond to the given pattern, the query will succeed and return the number 11.

Example number 4. To_date function parameters
Let the default date format be DD.MON.RR, and the date language be Russian, will the following query work:

 -- №11 select to_date(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual; 

A similar query featured in one of the discussions on the Tom Tom page. The request trap is that we are trying to convert a date (sysdate) to a date. If the request looked like this:

 -- №12 select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual; 

That execution would have been successful, and he returned the string '09 / 15/2011 23:00:11 '. However, the to_date function expects a string as the first parameter; therefore, an implicit date conversion to a string first occurs (which is equivalent to calling to_char (sysdate) with a default mask). The result of this conversion is the string '15 .09.11', then the to_date call occurs. Thus, query number 11 is equivalent to the following query:

 -- №13 select to_date('15.09.11','mm/dd/yyyy hh24:mi:ss') from dual; 

As it is not difficult to be convinced, the request №13 cannot be executed, since the line '15 .09.11 'does not correspond to the set mask, accordingly, the request №11 also cannot be executed.

Setting the default date format
The default date format is defined by two parameters: NLS_DATE_FORMAT (responsible for the format itself) and NLS_DATE_LANGUAGE (responsible for the language that will be used when writing the names of days, months, etc.). If these parameters are not explicitly set, then their values are set based on the NLS_LANG parameter .

There are three levels at which you can set the date format:

1. DB level:

    select * from nls_database_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); 

   The parameters of this level are set when creating the database and are written in the file init.ora.
2. Instance Level:

    select * from nls_instance_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); 

   The parameters of this level are set at the start of the instance and can be changed using the ALTER SYSTEM command.
3. Session Level:

    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); 

   Parameters at this level can be changed with the ALTER SESSION command. Also the value of these parameters can be checked using the query:

     select SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT'), SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') from dual; 

The parameters of each next level “overwrite” the parameters of the previous, i.e. if you have set session level parameters, then the parameters of the other levels will not affect your session. To set a single date format for all sessions, Tom suggests using an ON-LOGON trigger in his column :

 create or replace trigger data_logon_trigger after logon ON DATABASE begin execute immediate 'alter session set nls_date_format = ''dd/mm/yyyy'''; end; 

Example number 5. Format DD.MM.RR vs DD-MON-RR
Paying attention to the formatting of dates by default was caused by a certain oddity in the implicit conversion of strings to a date. Let's consider the following example:

 -- №14 --     alter session set NLS_DATE_FORMAT='DD.MM.RR'; --     alter session set NLS_DATE_LANGUAGE='AMERICAN'; --    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --    select to_date('11.09.11') from dual; select to_date('11.SEP.11') from dual; 

It is logical to assume that the conversion of the string '11 .09.11 'to the date will be successful, and the string '11 .SEP.11' will not. However, this is not the case; both conversions will succeed. At first, I assumed that in case of impossibility to convert the string by the session mask, Oracle tries to use other level masks (the DB level mask is set to 'DD-MON-RR'). Reading the documentation showed that this is not the case, and Oracle is guided by the principles described in the previous paragraph.

Let's try another example:

 -- №15 --     alter session set NLS_DATE_FORMAT='DD.MON.RR'; --     alter session set NLS_DATE_LANGUAGE='AMERICAN'; --    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --    select to_date('11.09.11') from dual; select to_date('11.SEP.11') from dual; 

If you think that the result will be identical to the previous query, then you are mistaken. One of the transformations will fail. In this case, the string '11 .09.11 'does not match the pattern. Is this a mystic?

Unfortunately no. Reading the documentation showed that there are rules for automatic replacement of date formatting elements. Below is a table of substitutions.

Original Format Element	Placement of the Original
'MM'	'MON' and 'MONTH'
'MON	'MONTH'
'MONTH'	'MON'
'Yy'	'YYYY'
'RR'	'RRRR'

Looking at the contents of this table, it becomes clear that the format 'DD.MM.RR' implicitly contains the format 'DD.MON.RR' (and also 'DD.MONTH.RR' and others), but in the format 'DD .MON.RR 'format' DD.MM.RR 'is not present, which explains the behavior of queries №14 and №15.

Example number 6. RR vs YY format
For most users, the differences between the RR and YY masks are well known, but there are also those to whom this information will prove useful. We proceed immediately to the consideration of the example. What data will return the following requests:

 -- №16 select to_date('11','RR') "RR", to_date('11','YY') "YY" from dual; -- №17 select to_date('99','RR') "RR", to_date('99','YY') "YY" from dual; 

Both of the above queries will succeed and return the dates in accordance with the rules described in example No. 1 for query No. 3. Thus, the day value in all received dates will be 01, and the month value is 09 (if you execute the query in September). The main question is what will be the value of the year?

As it is easy to assume, in request No. 16, by '11' I meant 2011, and both masks returned it to me, i.e. The result of query 16 is 01.09.2011 and 01.09.2011.

In query No. 17, under '99' I meant 1999, and here the views of the masks were divided: the RR mask returned the expected 1999, and the YY mask returned 2099, i.e. The result of query # 17 is 01/09/1999 and 09/01/2099.

Consider how these formatting elements work in more detail:

 -- №18 select to_date('00','RR') "00", to_date('49','RR') "49", to_date('50','RR') "50", to_date('99','RR') "99" from dual union all select to_date('00','YY') "00", to_date('49','YY') "49", to_date('50','YY') "50", to_date('99','YY') "99" from dual; 

As can be seen from query №18, the differences in the work of masks begin in the 50s, i.e. The YY format always returns the year in the current century, and RR perceives the year <50 as the year of the current century, and the year> 50 as the year of the last century. In fact, the values ​​of to_date ('99 ',' RR ') = 1999 and to_date ('00', 'RR') = 2000 are true only if the current date is less than 2051, after it to_date ('99 ',' RR ' ) = 2099 and to_date ('00 ',' RR ') = 2100. If you want to experiment, you can change the server time to 2051 and see how the result of query No. 18 changes, just don't do it on running systems! If the server time changes, sysdate will change and it’s better not to even think about what headache you will get in all reports, log tables, etc. The general rules for converting a two-digit year to a four-digit year using the RR format are as follows :
If the specified two-digit year is 00 to 49, then

• This is the last two digits of the current year.
• The last two digits of the past two digits of the current year are 1 greater than the first two digits of the current year.

If the specified two-digit year is 50 to 99, then

• The last two digits of the current year are one of the last two digits of the current year.
• The last year’s digits of the current year are 50 to 99, then the last year’s digits of the current year.

Please note, I am only talking about the case of setting the year with two digits, if you specify the year with four digits, then the mask YY will be automatically replaced by the mask YYYY, and the mask RR - RRRR.

 -- №19 select to_date('1950','RR') "50RR", to_date('1950','YY') "50YY" from dual; 

Therefore, query number 19 will return 1950 in both cases.

Example number 7. Some other examples
At the end of the review we will consider a bit of exotic. Will there be an error resulting from the following query:

 -- №20 select DATE '1928-12-25' from dual; 

If you decide that this is a meaningless entry, then you are mistaken - this is quite a correct setting of the date in accordance with the ANSI standard, the request №20 will be executed successfully and will return 12/25/1928.

Which query will fail?

 -- №21 select to_date('1998--25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') from dual; -- №21 select to_date('1998--25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=RUSSIAN') from dual; 

This example is intended to demonstrate the presence of the third parameter in the to_date function. This parameter allows you to set the value of one of the NLS (National Language Support) parameters only for this to_date function call. Setting the NLS parameters for the session, we considered in the example number 5. The key difference between queries No. 20 and No. 21 is not in the name of the month (the MON mask is automatically replaced by the MONTH mask, as described in Example No. 5), but in the indication of different date languages. Request №21 expects the name of the month in English and, accordingly, will not be executed, request №22 expects the name of the month in Russian and will be executed successfully.

In which case the following table declaration may cause errors when inserting data?

 -- №23 create table for_test (a number, b date default to_date('11.09.2011')); 

Every time a session with the default date format 'DD.MON.RR' will insert an insert indicating only the value of the first column

 insert into for_test(a) values(1); 

an error will occur.

Features of the display of dates in various applications

What affects the date display
This section was added after the publication of the article due to the recommendations indicated in the comments. The following is true for both date display and number display. You may have gotten the dates in a different format than the ones in the examples above. If the settings for your session are as specified in the examples, then this seems at least strange.

The truth is that when you run a query

 -- №24 select sysdate from dual; 

You get the date, but to display the result on the screen, the specific utility with which you access the database should convert the date to a string. Thus, to display dates (and numbers), to_char is implicitly called, i.e. we have a classic case of implicit conversion (this conversion is for display only, its results do not participate in any calculations and do not affect anything other than the display of data). If there is an implicit conversion, then there is also a mask on which it is performed. In the classic case, this should be the mask set for the session, i.e. the mask specified in the NLS_DATE_FORMAT parameter of the nls_session_parameters table with which we have been actively working throughout the article.

Let's check the work of some applications. We will check using the following script:

 -- №25 --     select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --  select sysdate from dual; --     alter session set NLS_DATE_FORMAT='DD.MON.RR hh24:mi:ss'; --  select sysdate from dual; 

Check what parameters sqlplus uses to display dates.

Fig. 1. The result of the query №25 in sqlplus.

As can be seen from Figure 1, the date display format changes depending on the session settings, i.e. sqlplus uses session settings. This makes it easier to understand the process of converting dates to strings and vice versa, since the same masks are used for both conversion and display.

Some advanced development tools use their own NLS settings that are not related to Oracle settings. As an example, let's check which parameters PL / SQL Developer uses to display dates. To do this, we will execute query 25 in it.

Fig. 2. The result of query # 25 in PL / SQL Developer.

As can be seen from Figure 2, the date display format does not change when changing session settings. Moreover, if you look closely, you can see that the first and second results of the date display on the screen did not match the session parameters (in the first case, the displayed date had a year in four-digit format, and the mask indicated the year in two-digit format). This means that the utility uses its own NLS settings; in the case of PL / SQL Developer, their location is shown in Figure 2. 3

Fig. 3. Set NLS parameters for displaying dates in PL / SQL Developer.

What can be harmful NLS settings utilities
Displaying the date in a format different from the session format is harmful for one reason - it misleads the user and can lead to errors. Perform the following query in sqlplus and PL / SQL Developer:

 -- №26 --     alter session set NLS_DATE_FORMAT='DD.MON.RR hh24:mi:ss'; --  select sysdate from dual; --         select to_date() from dual; 

Instead of XXXXXXXX, we will insert the data obtained from the previous line into the last line of the query.

The results of the query are presented in the figures below.

Fig. 4. The result of query # 26 in sqlplus.


Fig. 5. The result of query # 26 in PL / SQL Developer.

Why in sqlplus the displayed data was successfully converted to a date, and the data displayed on the screen by PL / SQL Developer could not be converted? Because for conversion, Orakl uses the data format specified in the session, and the data output by PL / SQL Developer has been brought to be displayed in its own format, which is different from the session format.

Conclusion

As a conclusion I want to remind you that in almost every post about work with dates, Tom Kite speaks of the need to use explicit transformations and the mandatory indication of a mask. “When converting a string to a date, never rely on the default date format, always explicitly set the mask,” something like this sounds like. Additional examples and possible errors when working with date conversion can be found using the Tom Tom page.

Since working with dates took up the whole article, there are a lot of interesting questions left over which I would like to consider. Most likely, the third part of the article will appear as soon as I have free time.