Gotchas with the Oracle DATE Datatype
DATE is a common datatype. Most transactions in a database have a date associated with them for tracking purposes. When was the order made? When is the order expected to be delivered? When was it actually delivered? And so on. Querying dates, however, takes some attention to detail as there are many details to consider that can cause the wrong returns.
The DATE Datatype
A DATE is stored in Oracle as a fixed-length 7-byte field. The data stored is the century, year, month, day, hour, minute and second. It doesn’t include timezone or fractions of a second.
Depending on the NLS_DATE_FORMAT entry in your settings, dates are converted for your convenience to a string when it is queried. This setting will also assist in implicit conversions from strings to dates. For instance, if the NLS_DATE_FORMAT is at the default setting, it will be in the format DD-MON-RR (Day-Month-Year/Century). Because of this, it will understand the string ’01-JAN-2015′ when compared to a date string.
DATE Storage and the Hidden Data
Because of the NLS_DATE_FORMAT and it’s default of DD-MON-RR, you may get under the impression that a DATE column in your table has only date information. Try this to see what I mean. Today’s date will be returned in the queries below.
SQL> create table example_date (ex_date date); Table created. SQL> insert into example_date values (sysdate); 1 row created. SQL> select sysdate from dual; SYSDATE --------- 19-JUN-15 SQL> select * from example_date; EX_DATE --------- 19-JUN-15
Now, try the following query:
SQL> select to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') 2 from example_date; TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 16:50:15
WHERE DID THAT TIME COME FROM?! It’s always there, whether you realize it or not. When inserting into a DATE field, you will set it in code. If that information includes hours, minutes and seconds, it will be saved in the database.
TRUNC will remove the time information from a date.
SQL> delete from example_date; 1 row deleted. SQL> insert into example_date values (trunc(sysdate)); 1 row created. SQL> select to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') from example_date; TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 00:00:00
If you exclude the time information, then only the date will be stored.
SQL> delete from example_date;
1 row deleted.
SQL> insert into example_date values ('19-JUN-2015');
1 row created.
SQL> select to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') from example_date;
TO_CHAR(EX_DATE,'DD-MON-YYYYH
-----------------------------
19-JUN-2015 00:00:00
Again, this depends on the NLS_DATE_FORMAT set for the database. DD-MON-RR is the default setting. That setting is used to automatically convert dates to strings and strings to dates.
Time or No Time?
When designing tables with DATE fields, decide on time storage. Do you need the time? If not, remove it. This will save you pain in the queries later to come. Always enter the data without the time or using TRUNC to remove the time. If you need the time, then by all means include time. You can possibly use SYSDATE for the column value if you’re stamping it with current date and time as it includes the entire date-time value down to the second.
Querying Dates with Times
Often times, queries on date columns aim to return a range. “Give me the last week’s worth of data.” Considering what was mentioned before, remember that a DATE field with times in it has to consider the times.
This May Not Work
The following may not work because it isn’t considering that the dates in the column can be for any time but the sysdate includes times. If the time the query ran is 11am, you will only get orders from seven days ago starting at 11am. So, you’re leaving it to change that orders seven days ago started at 11am.
SQL> SELECT order_id 2 FROM orders 3 WHERE order_date >= SYSDATE - 7;
In the following example, we insert another date value including the time. We query what we have in the table now, including a to_char to show us the time, and we see we have the first date inserted – a truncated value – and the one just inserted that included time.
SQL> insert into example_date values (sysdate); 1 row created. SQL> select to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') from example_date; TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 00:00:00 19-JUN-2015 17:15:26
When we query the table and don’t include a time, it returns the value with 00:00:00. This is because of the NLS_DATE_FORMAT specified as DD-MON-RR. It is automatically converting the string to a date using the format set in the system.
SQL> select to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') 2 from example_date 3 where ex_date = '19-JUN-2015'; TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 00:00:00
So, back to the original orders table example, the midnight-exactly order wouldn’t be returned by the query ran at 11am.
This Will Always Work
Because the data was stored with times, you have to truncate the date you are searching by to make the time start at midnight. If the results you want are to include all orders for that day beginning with 00:00:00, then this is the query for you.
SQL> SELECT order_id 2 FROM orders 3 WHERE order_date >= TRUNC(SYSDATE) - 7;
Another technique that always works is to convert a string to date. Include the time if it’s other than 00:00:00 because the NLS_DATE_FORMAT we have set will automatically convert a string to date with just DD-MON-RR and the rest as 00:00:00.
SQL> SELECT order_id
2 FROM orders
3 WHERE order_date >= to_date('12-JAN-15');
Date Ranges
Let’s say you want to specify a date range. The last day would have to end at 23:59:59 to get all of the possibilities for that final day. For this, we would have to convert a string to a date that includes the time.
SQL> SELECT order_id
2 FROM orders
3 WHERE order_date >= to_date('12-JAN-15')
4 and order_date <= to_date('19-JAN-2015 23:59:59','DD-MON-YYYY HH24:MI:SS');
Another way to write this would be with a BETWEEN condition.
SQL> SELECT order_id
2 FROM orders
3 WHERE order_date BETWEEN to_date('12-JAN-15')
4 AND to_date('19-JAN-2015 23:59:59','DD-MON-YYYY HH24:MI:SS');
Let’s play with this a little more. Enter a date using to_date and include 23:59:59 as a time.
SQL> insert into example_date values
2 (to_date('19-JUN-2015 23:59:59','DD-MON-YYYY HH24:MI:SS'));
1 row created.
SQL> insert into example_date values (sysdate+1); 1 row created.
Now we’ll write a query that will give us the dates according to the WHERE clause.
Remember that TRUNC removes the time portion.
Between trunc(sysdate) and trunc(sysdate+1) – today at 00:00:00 and tomorrow at 00:00:00.
SQL> SELECT to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') 2 FROM example_date 3 WHERE ex_date BETWEEN trunc(sysdate) and trunc(sysdate+1); TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 00:00:00 19-JUN-2015 17:15:26 19-JUN-2015 23:59:59
Now we’ll look at tomorrow at 00:00:00 to the next day at 00:00:00. This will be the second date we inserted as sysdate+1.
SQL> SELECT to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') 2 FROM example_date 3 WHERE ex_date BETWEEN trunc(sysdate+1) and trunc(sysdate+2); TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 20-JUN-2015 18:00:04
This time, we leave off the TRUNC statements and are now at the mercy of what time it is along with the dates stored. If we were only looking at dates and not considering the times stored with them, this would seem very wrong to us. After all, we queried for today and tomorrow.
SQL> SELECT to_char(ex_date,'DD-MON-YYYY HH24:MI:SS') 2 FROM example_date 3 WHERE ex_date BETWEEN sysdate and sysdate+1; TO_CHAR(EX_DATE,'DD-MON-YYYYH ----------------------------- 19-JUN-2015 23:59:59 20-JUN-2015 18:00:04
….but our data has times stored with the dates and sysdate also has times included in it’s date.
SQL> SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'), 2 to_char(sysdate+1,'DD-MON-YYYY HH24:MI:SS') 3 FROM dual; TO_CHAR(SYSDATE,'DD-MON-YYYYH TO_CHAR(SYSDATE+1,'DD-MON-YYY ----------------------------- ----------------------------- 19-JUN-2015 18:06:45 20-JUN-2015 18:06:45
Moral of the Story
When comparing dates:
- Always consider times stored in the date field
- Always consider times of the conditions used to compare against them
- Always consider the NLS_DATE_FORMAT and how it will display dates possibly without times
One thought on “Gotchas with the Oracle DATE Datatype”