TECHNOLOGY: SQL 101
Selecting a Type That Is Right for YouBy Melanie Caffrey
Part 8 in a series on the basics of the relational database and SQL
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 Perfect Format for Your Date
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.
Dates with Strings Attached
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.
Taking Time with Your Dates
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.
Cutting Your Date Short
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.
A System for Getting Your Dates Right
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. You can review the documentation for more details at bit.ly/PR7GQh and bit.ly/NOgf01. The next installment of SQL 101 will discuss aggregate functions.
Melanie Caffrey is a senior development manager at Oracle. She is a coauthor of Expert PL/SQL Practices for Oracle Developers and DBAs (Apress, 2011) and Expert Oracle Practices: Oracle Database Administration from the Oak Table (Apress, 2010).
Send us your comments