Digital photo titled river-14

Dates in Oracle 8i and earlier versions

a soon to be obsolete part of SQL for Web Nerds by Philip Greenspun
From version 9 onwards it is possible to represent points in time and time intervals using ANSI SQL data types such as timestamp and interval. We strongly recommend that you use the newly available ANSI data types when building new applications. Visit the main chapter on dates for help in this direction.

If you're stuck using an obsolete version of Oracle or adding to someone's old data model in a new version of Oracle, read on...

Before talking about inserting or querying date information from an older version of Oracle, it is worth recalling what kinds of date/time information those older versions are capable of representing. Here is a snippet from the data modeling chapter:

Dates and Date/Time Intervals
date A point in time, recorded with one-second precision, between January 1, 4712 BC and December 31, 4712 AD. You can put in values with the to_date function and query them out using the to_char function. If you don't use these functions, you're limited to specifying the date with the default system format mask, usually 'DD-MON-YY'. This is a good recipe for a Year 2000 bug since January 23, 2000 would be '23-JAN-00'. On ArsDigita-maintained systems, we reset Oracle's default to the ANSI default: 'YYYY-MM-DD', e.g., '2000-01-23' for January 23, 2000.
number Hey, isn't this a typo? What's number doing in the date section? It is here because this is how Oracle represents date-time intervals, though their docs never say this explicitly. If you add numbers to dates, you get new dates. For example, tomorrow at exactly this time is sysdate+1. To query for stuff submitted in the last hour, you limit to submitted_date > sysdate - 1/24.
When using Oracle 8i and earlier versions, the important things to take away from this are the following:

Import and Export

You can get dates out of Oracle simply by selecting the column. Here we ask for the posting time of all bboard messages in the last 24 hours: 

select posting_time 
from bboard
where posting_time + 1 > sysdate;

POSTING_TI
----------
1999-02-01
1999-02-01
1999-02-01
1999-02-01
...
Because we didn't say how we wanted Oracle to convert the date to a string, Oracle used the default ANSI date format. 

select to_char(posting_time,'YYYY-MM-DD HH24:MI:SS') as precise_time
from bboard
where posting_time + 1 > sysdate;

PRECISE_TIME
-------------------
1999-02-01 19:08:57
1999-02-01 19:10:33
1999-02-01 19:10:34
1999-02-01 19:23:58
1999-02-01 19:32:25
1999-02-01 19:34:01
...
How about getting dates into Oracle? Suppose that your site was featured on television at 7:00 pm on February 1, 1999. You are interested in the number of bboard postings around that time. Let's start with a simple query: 

select count(*) 
from bboard 
where posting_time > '1999-02-01';

  COUNT(*)
----------
       445
Notice that we imported a date into Oracle just now. Oracle automatically coerced the string "1999-02-01" into a date. Which date? Oracle dates are precise down to the second. The string "1999-02-01" is only a day. By convention, Oracle assumes you mean the instant after midnight on that date, as though you'd typed 

select count(*) 
from bboard 
where posting_time > '1999-02-01 00:00:00';

ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
Oops. Oracle isn't willing to assume anything about the date format unless you're using the default. We need to call the built-in SQL function to_date: 

select count(*) 
from bboard 
where posting_time > to_date('1999-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
       445
Let's get back to our TV show. How many postings were received in the hour after it aired? 

select count(*) 
from bboard 
where posting_time > to_date('1999-02-01 19:00:00','YYYY-MM-DD HH24:MI:SS')
and   posting_time <= to_date('1999-02-01 20:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
	10
Compared to a week earlier? 

select count(*) 
from bboard 
where posting_time > to_date('1999-01-25 19:00:00','YYYY-MM-DD HH24:MI:SS')
and   posting_time <= to_date('1999-01-25 20:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
	15
Looks as though TV exposure isn't working so great for your site...

Reporting

Suppose that you're the proprietor of an ArsDigita Shoppe (see http://eveander.com/arsdigita/free-tools/shoppe for the source code). You want to see how well one of your products (ID #143) has been selling: 

select to_char(shipped_date,'YYYY') as shipped_year, 
       to_char(shipped_date,'Q') as shipped_quarter, 
       sum(price_charged) as revenue
from sh_orders_reportable
where product_id = 143
and shipped_date is not null
group by to_char(shipped_date,'YYYY'), to_char(shipped_date,'Q')
order by to_char(shipped_date,'YYYY'), to_char(shipped_date,'Q');

SHIPPED_YEAR	     SHIPPED_QUARTER	     REVENUE
-------------------- -------------------- ----------
1998		     2				1280
1998		     3				1150
1998		     4				 350
1999		     1				 210
This is a hint that Oracle has all kinds of fancy date formats (covered in the reference links below). We're using the "Q" mask to get the calendar quarter. We can see that this product started shipping in Q2 1998 and that revenues trailed off in Q4 1998.

Reference

philg@mit.edu