Selecting a Type That Is Right for You

Part 8 in a series on the basics of the relational database and SQL

November/December 2012

Part 7 in this series, “From Floor to Ceiling and Other Functional Cases” (Oracle Magazine, September/October 2012), introduced common SQL number functions and showed how your queries can use them to modify the appearance of numeric result set data. It also introduced SQL substitution functions and showed how you can use them to manipulate result set data to convey more-meaningful results. Similarly, you can use SQL date functions and datatype conversion functions to manipulate data so that it displays differently from how it is stored in the database. This article introduces you to some of the more commonly used SQL date functions, along with some useful datatype conversion functions.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition 11g Release 2.

If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_101 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_101 schema that is required for this article’s examples. (View the script in a text editor for execution instructions.) Some of the examples also use the DUAL table. Recall that DUAL is an Oracle system table owned by the SYS user, not the SQL_101 schema. DUAL contains no meaningful data itself, but it is useful to query it as a way to experiment with functions that work on literals.

The DATE datatype is stored in Oracle Database in an internal format that consists of both date and time information: the century, year, month, day, hour, minute, and second. For input and output of dates, every Oracle Database instance has a default date format model (also called a mask) that is set by the NLS_DATE_FORMAT initialization parameter. (Initialization parameters determine the default settings for Oracle Database instances. Users who have appropriate permissions can change some of these parameters on a per-database, per-instance, or per-session basis.) When you first query the data stored in a table column with a DATE datatype, Oracle Database displays it with a format mask of either DD-MON-YYYY or DD-MON-RR, depending on which is set as the default.

The RR format mask, which represents a two-digit year, was introduced to deal with end-of-century issues such as the Y2K problem. With RR, a two-digit year can refer to a year in the previous, current, or next century—depending on the current year and the two-digit year specified in the query. Table 1 shows the relationship between the current year, the range of two-digit year combinations, and the corresponding century referred to as a result.

For example, the last two digits of the current year (2012) are 12, which falls between 00 and 49. A SQL query issued during 2012 that specifies an RR year value of 15, therefore, refers to the year ending in 15 (2015) in the current century (the twenty-first), because 15 is between 0 and 49. A query issued in 2012 that specifies an RR year value of 98 refers to the year ending in 98 (1998) in the previous century (the twentieth), because 98 is between 50 and 99.

The query in Listing 1 uses the EMPLOYEE table in the sample schema for this article. The query displays employees sorted from most recent to least recent date of hire. As you can see, the hire date data is displayed in DD-MON-RR format. For example, it shows that Roger Friedli was hired on 16-MAY-07. To change the way this data is displayed, you use the TO_CHAR conversion function in conjunction with a format model of your choosing. (You had a brief introduction to TO_CHAR in the last installment, where you saw that it can be used to convert a number to a text string.)

Code Listing 1: Display date data in the Oracle Database default date format

SQL> set feedback on
SQL> select first_name, last_name, hire_date
  2    from employee
  3  order by hire_date desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Theresa          Wong             27-FEB-10
Thomas           Jeffrey          27-FEB-10
mark             leblanc          06-MAR-09
michael          peterson         03-NOV-08
Roger            Friedli          16-MAY-07
Betsy            James            16-MAY-07
Matthew          Michaels         16-MAY-07
Donald           Newton           24-SEP-06
Frances          Newton           14-SEP-05
Emily            Eckhardt         07-JUL-04
10 rows selected. 

The query in Listing 2 modifies the way the date data from Listing 1 is displayed. To convert data of DATE datatype to a specific date format model, TO_CHAR takes one required parameter and one optional parameter. The required parameter is data of DATE datatype from a column, expression, or literal. The optional parameter is a textual format-mask representation of the date to be displayed. In Listing 2, the default format mask of DD-MON-RR is changed to display as YYYY-MM-DD.

Code Listing 2: Display date data in a different format by using TO_CHAR with a format mask

SQL> select first_name, last_name, TO_CHAR(hire_date, 'YYYY-MM-DD') hire_date
  2    from employee
  3  order by hire_date desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          2010-02-27
Theresa          Wong             2010-02-27
mark             leblanc          2009-03-06
michael          peterson         2008-11-03
Roger            Friedli          2007-05-16
Betsy            James            2007-05-16
Matthew          Michaels         2007-05-16
Donald           Newton           2006-09-24
Frances          Newton           2005-09-14
Emily            Eckhardt         2004-07-07
10 rows selected.

Listing 3 demonstrates that the second parameter for TO_CHAR is optional. If it is left off, the format mask of the date data returned will simply be the default format mask. Note also that the datatype of the date returned is VARCHAR2. The output from Listing 3 is sorted by HIRE_DATE in descending order, but in character, not date, descending order. So, be aware that when you apply the TO_CHAR conversion function, your data is returned as character strings; you should plan and sort accordingly.

Code Listing 3: Default date format mask is used when optional parameter is not provided

SQL> select first_name, last_name, TO_CHAR(hire_date) hire_date_formatted
  2    from employee
  3  order by hire_date_formatted desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-10
Theresa          Wong             27-FEB-10
Donald           Newton           24-SEP-06
Roger            Friedli          16-MAY-07
Betsy            James            16-MAY-07
Matthew          Michaels         16-MAY-07
Frances          Newton           14-SEP-05
Emily            Eckhardt         07-JUL-04
mark             leblanc          06-MAR-09
michael          peterson         03-NOV-08
10 rows selected.

Just as you can convert a date to a string, you can convert a string literal to a date. The resulting expression can be compared with any other column’s data of DATE datatype or another date expression. You perform the conversion by applying the TO_DATE conversion function to a text string, as shown in Listing 4. The query in Listing 4 not only returns all employees whose HIRE_DATE value is found to be greater than the date value 01-JAN-2008; it also demonstrates that the TO_DATE conversion function can be used in WHERE clauses as well as SELECT lists. The TO_DATE function is applied to the string literal 01-JAN-2008, with a format mask that helps the database interpret the supplied literal as a date.

Code Listing 4: Use the TO_DATE conversion function in a WHERE clause

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
  2    from employee
  3   where hire_date > TO_DATE('01-JAN-2008', 'DD-MON-YYYY')
  4  order by hire_date desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010
Theresa          Wong             27-FEB-2010
mark             leblanc          06-MAR-2009
michael          peterson         03-NOV-2008
4 rows selected.

When you provide a format mask to the TO_DATE function, the mask you choose must be the same as the one used in the string literal you supply. If the two do not agree, you will receive an error message similar to the one shown in Listing 5. When you convert a text literal, it is good practice to use the TO_DATE conversion function and explicitly specify an appropriate format mask. This way, your statement can be interpreted independently of any database, instance, or session default date settings.

Code Listing 5: Error when the format mask does not match the provided string literal

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
  2    from employee
  3   where hire_date > TO_DATE('01-JAN-2008', 'MM/DD/RR')
  4  order by hire_date desc, last_name, first_name;
 where hire_date > TO_DATE('01-JAN-2008', 'MM/DD/RR')
                           *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

Oracle Database will perform implicit date conversion where it can, if (and only if) the literal is already in the default date format. However, I do not recommend that you allow it to do so, because your code will be more fragile and less likely to perform well long-term. Listing 6 shows a query that relies on the default date format in Oracle Database and its ability to perform implicit date conversion on a string literal. Compare the result in Listing 6 with that in Listing 7, which also attempts to perform an implicit date conversion. The query in Listing 7 fails because the database cannot interpret the date format mask of the literal value being compared with the values in the HIRE_DATE column of the EMPLOYEE table.

Code Listing 6: Implicit date conversion (not recommended) returns a result set

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
  2    from employee
  3   where hire_date > '01-JAN-2008'
  4  order by hire_date desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010
Theresa          Wong             27-FEB-2010
mark             leblanc          06-MAR-2009
michael          peterson         03-NOV-2008
4 rows selected.

Code Listing 7: Attempted implicit date conversion fails

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
  2    from employee
  3   where hire_date > '01/01/2008'
  4  order by hire_date desc, last_name, first_name;
 where hire_date > '01/01/2008'
                   *
ERROR at line 3:
ORA-01843: not a valid month

Because the default date format can be changed, it is best not to allow your queries to rely on an expected default format. Instead, always use the TO_DATE function on date string literals. One way to find out which default date format your current session is using is to execute the query shown in Listing 8. The SYS_CONTEXT function can be used by any session (and, therefore, any user) to see current session attributes.

Code Listing 8: Find the default date format for your current session

SQL> select sys_context ('USERENV', 'NLS_DATE_FORMAT')
  2    from dual;
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
————————————————————————————————————————————————
DD-MON-RR
1 row selected.

Recall that the Oracle DATE datatype includes a time component. You can either ignore the time component, as the examples in this article have done so far, or you can include it for display or comparison purposes. Listing 9 shows a query that includes the time component from each HIRE_DATE value for every employee listed in the EMPLOYEE table. Note that all the employee records except the one for Theresa Wong show a time value of 12:00:00. If you do not include a time when inserting a value into a column with a DATE datatype, the time will default to midnight (12:00:00 a.m. or 00:00:00 military time). To display or compare a date value in military time, use the HH24 format mask instead of HH.

Code Listing 9: Display the time component of a value with a DATE datatype

SQL> set lines 32000
SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
  2    from employee
  3  order by hire_date desc, last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010 12:00:00
Theresa          Wong             27-FEB-2010 09:02:45
Donald           Newton           24-SEP-2006 12:00:00
Roger            Friedli          16-MAY-2007 12:00:00
Betsy            James            16-MAY-2007 12:00:00
Matthew          Michaels         16-MAY-2007 12:00:00
Frances          Newton           14-SEP-2005 12:00:00
Emily            Eckhardt         07-JUL-2004 12:00:00
mark             leblanc          06-MAR-2009 12:00:00
michael          peterson         03-NOV-2008 12:00:00
10 rows selected.

Unless you know the exact time of the date values on which you’d like to filter—or unless all the time portions for your date values are already set to midnight—using date values in your WHERE clauses can produce unexpected results. Consider the query in Listing 10. You know from the results in the previous listings that two employees were hired on February 27, 2010, yet only one is returned in Listing 10’s result set. The reason is that the TO_DATE function in the WHERE clause does not specify an exact time, so Oracle Database assumes that the time is midnight and returns only those records that contain the specified date value and midnight as the time component.

Code Listing 10: WHERE clause using TO_DATE might not capture all possible values

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
  2    from employee
  3   where hire_date = TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
  4  order by last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010 12:00:00
1 row selected.

When you would like to be able to filter on a certain date but do not want to have to include each individual time component, you can use a couple of different methods. One method is to include the TRUNC function (introduced in the previous installment in this series). It, like the TO_CHAR function, works not only on numbers but also on date values. The TRUNC function helps cut off the time portion of a date if no optional format parameter is passed to it. This can be useful for date comparison purposes. Listing 11 shows a revised version of the query from Listing 10. As you can see, eliminating the time portion of the values in the HIRE_DATE column enables the comparison against the date value 27-FEB-2010 to retrieve all records with a HIRE_DATE value of 27-FEB-2010, irrespective of the time. The truncated HIRE_DATE value is made into a date only value to be compared with the corresponding date only value returned from the result of applying the TO_DATE function on the literal string 27-FEB-2010 with a date-only format.

Code Listing 11: Truncate the time from a DATE value to return all records for a particular day

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
  2    from employee
  3   where TRUNC(hire_date) = TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
  4  order by last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010 12:00:00
Theresa          Wong             27-FEB-2010 09:02:45
2 rows selected.

Be aware, however, that you might sacrifice performance by applying a function to your table column values in a WHERE clause. Indexes (used to assist with data access efficiency—and not discussed in this series) can improve query performance in certain situations. Applying a function to a table column has the effect of ensuring that an index on the column might never be used. Also, this function would be applied to every value in that column for every row. Both actions are extreme performance inhibitors. Therefore, another method you can use is to specify a date range outside of the date(s) you would actually prefer to filter on. The query in Listing 12 retrieves the same result set as the query in Listing 11. The difference between the two is that the query in Listing 12 does not apply a function to the HIRE_DATE column data. Instead, it chooses a range just outside of the desired date(s) and encloses the filtered date data inside this range of values.

Code Listing 12: Date range that returns records for a particular day

SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
  2    from employee
  3   where hire_date >= TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
  4     and hire_date <  TO_DATE('28-FEB-2010', 'DD-MON-YYYY')
  5  order by last_name, first_name;
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          27-FEB-2010 12:00:00
Theresa          Wong             27-FEB-2010 09:02:45
2 rows selected.

You will often need to perform date arithmetic. A useful built-in function (one already built into Oracle Database) is SYSDATE. This function returns the current date and time that are set on the operating system of the computer on which the database resides. It takes no parameters. Listing 13 shows an example of using the SYSDATE function to return and display the current date and time.

Code Listing 13: The SYSDATE function

SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') sysdate_with_time
  2    from dual;
SYSDATE   SYSDATE_WITH_TIME
———————   ——————————————————————————————
08-AUG-12 08-AUG-2012 14:25:08
1 row selected.

SYSDATE can be extremely useful in date arithmetic. Listing 14 shows how many days are left in 2012 from the current date (August 8, 2012, in the example). Note that if the SYSDATE value were not truncated, the returned DAYS_TILL_2013 value would include some fraction of the SYSDATE value (to account for the time component). Because it is truncated, however, the entire current date is subtracted from January 1, 2013, to arrive at the result of 146 days left in the year. Listing 15 uses SYSDATE and date arithmetic (using a date function called MONTHS_BETWEEN) against the HIRE_DATE column of the EMPLOYEE table, to show the number of years of service for each employee.

Code Listing 14: SYSDATE used in date arithmetic

SQL> select SYSDATE, (TO_DATE('01-JAN-2013', 'DD-MON-YYYY') - TRUNC(SYSDATE))
Days_till_2013
  2    from dual;
SYSDATE     DAYS_TILL_2013
——————————  ——————————————
08-AUG-12   146
1 row selected.

Code Listing 15: SYSDATE and date arithmetic combined with DATE data

SQL> select substr(last_name, 1, 10) last_name, substr(first_name, 1, 10)
first_name, hire_date, ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), TRUNC(HIRE_
DATE))/12, 2) YEARS_OF_SERVICE
  2    from employee
  3  order by years_of_service desc, last_name, first_name;
LAST_NAME  FIRST_NAME  HIRE_DATE  YEARS_OF_SERVICE
—————————  ——————————— —————————    ——————————————
Eckhardt   Emily       07-JUL-04             8.09
Newton     Frances     14-SEP-05              6.9
Newton     Donald      24-SEP-06             5.88
Friedli    Roger       16-MAY-07             5.23
James      Betsy       16-MAY-07             5.23
Michaels   Matthew     16-MAY-07             5.23
peterson   michael     03-NOV-08             3.77
leblanc    mark        06-MAR-09             3.42
Jeffrey    Thomas      27-FEB-10             2.45
Wong       Theresa     27-FEB-10             2.45
10 rows selected.

Another method for performing date arithmetic is to use the BETWEEN operator, as demonstrated by the query in Listing 16. Be aware, however, that the BETWEEN operator uses the midnight (or 00:00:00) time component of the upper-range value in a date-range comparison. To include all possible values for the date specified in the upper range of the date comparison, ensure that the date includes the full time component of your upper range. In the example in Listing 16, an upper-range date value of 27-FEB-2010 23:59:59 would have allowed both employee records with a HIRE_DATE value of 27-FEB-2010 to be included in the result set.

Code Listing 16: BETWEEN operator uses midnight in a date range comparison

SQL> select last_name, first_name, hire_date
  2    from employee
  3   where hire_date BETWEEN TO_DATE('26-FEB-2010', 'DD-MON-YYYY')
  4                       AND TO_DATE('27-FEB-2010', 'DD-MON-YYYY');
FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Jeffrey          Thomas           27-FEB-10
1 row selected.

This article has shown you a few of the most common date functions and how they can be used to manipulate the way data is displayed. You’ve seen how to use the TO_CHAR and TO_DATE conversion functions and have learned the differences between them. You now know that dates all contain a time component that can be used or truncated according to your needs. You’ve been introduced to the SYSDATE function and date arithmetic. Last but not least, you now know the pitfalls to be aware of when you use DATE comparisons in WHERE clauses with TO_DATE and BETWEEN—and what you can do to avoid unexpected results. By no means has this article provided an exhaustive list of the Oracle Database date and datatype conversion functions. The next installment of SQL 101 will discuss aggregate functions.

READ SQL 101, Parts 1–7

LEARN more about date and datatype conversion functions

READ more about
relational database design and concepts
Oracle Database Concepts 11g Release 2 (11.2)
Oracle Database SQL Language Reference 11g Release 1 (11.1)
Oracle SQL Developer User’s Guide Release 3.1

Oracle Database development essentials
Oracle Database 2 Day Developer’s Guide 11g Release 2 (11.2)

DOWNLOAD the sample script for this article