Working with Date Fields

                         Document #: US-13229,EN

Major subject: sql    Minor subjects: sql_syntax

Keywords: datatypes, tech_notes

Working with Date Fields - Equivalent to INGRES Release 
6 Technical Note #8 or Release 5 Note #86.

Expert note:
Working with Date Fields

INGRES provides a powerful syntax in both SQL and QUEL for working with
dates and time intervals.  The purpose of this document is to illustrate
the capabilities of date arithmetic and date functions, using several

o	Background
o	Operations and Functions
o	Combining Functions
o	Summary

A date is a value that is either an absolute date, like "02-sep-1987",
an absolute time, like "23:04:48", an absolute date and time, like "28-
feb-1987 12:01:58", a date interval, like "5 years 14 days", or a time
interval, like "8 hrs".  These are output by INGRES as a 25 character
string when date format is used.  The default output format for a date
is "dd-mmm-yyyy", and for a date and time it is "dd-mmm-yyyy hh:mm:ss".

Dates can be input in a variety of formats.  For the date November 15,
1982, INGRES would accept any one of the following as input: "11/15/82",
"11-15-82", "82.11.15", "15-nov-82".  There are other input formats,
listed in your query language reference manuals.  Please note that 
"today" is a legal input for a date field and will put the current 
date in the field.

Absolute time input for 10:30:00 could be "10:30:00", "10:30:00 pst",
or "10:30".  For absolute date and time, you can combine any of the 
three time formats with one of the following date formats:  mm/dd/yy
or dd-mmm-yy.  Therefore, there are six possible formats for absolute
date and time.  Please note that "now" is a legal input and will put 
the current date and time in the field.

Input formats for date intervals and time intervals use these units:
yrs,years,months,mos,days,hours,hrs,minutes,mins,seconds,secs.  There-
fore, some sample date interval and time interval inputs are:  "5
years","8 mos 14 days","23 hours","13 hrs 38 mins 43 secs".

Date values can range from 1-jan-1582 to 31-dec-2382. Time intervals can
range from -800(max) years to +800(max) years within the specified 
range of Date values.

Operations and Functions
The following operations and functions are available. Each is given with
an example.

1. absolute - absolute = interval

A parts supplier has a table called "delivery", with columns called
"customer", "placedate","shipdate", and "recvdate".  The columns with
column names ending in "date" have INGRES' date format.  The query is:
How many days did company XYZ wait from the time it placed its order
until it received the order?

SQL:  SELECT x=(delivery.recvdate - delivery.placedate) FROM delivery
      WHERE delivery.customer = 'XYZ';

QUEL: RETRIEVE (x=delivery.recvdate - delivery.placedate)
      WHERE delivery.customer = "XYZ"

2. absolute - interval = absolute OR absolute + interval = absolute

Using the same table described above, the query  is:   How  many  orders
were shipped more than 5 days after they were placed?

SQL:  SELECT x=COUNT(d.customer) FROM delivery d
      WHERE d.placedate < d.shipdate-'5 days';

QUEL: RANGE OF d IS delivery
      (x=COUNT(d.customer WHERE d.placedate < d.shipdate-"5 days" ))

3. interval - interval = interval OR interval + interval = interval

A pharmaceutical firm is testing two materials for capsules.  There is a
table called "results" with columns containing the drug name, the number
of days the drug remained effective using material #1 for  the capsule,
and the number of days for material #2.  The columns "mat1" and "mat2"
have INGRES' date format.  Dr. Jones wants to know the difference between
the effectiveness of the two materials, for each drug.

SQL:  SELECT drug,x=mat1 - mat2 FROM results;

QUEL: RETRIEVE (results.drug,x=results.mat1 - results.mat2)

4. date conversion

INGRES stores dates internally in a 12-byte format, and displays dates
as a 25-character string.  INGRES knows it is a date, and allows the
operations and functions being discussed in this document.  There are
times, however, when a user stores date information in c, vchar (SQL),
or text (QUEL) fields.  How can date arithmetic and functions be used on
such fields?

The date conversion function takes the data in a c, vchar (SQL), or text
(QUEL) field, and gives the internal representation of that date value
used by INGRES. This is useful for manipulating dates entered in fields
with c,vchar, or text formats.  The value entered in those fields must
have one of the date formats listed earlier or in the SQL and QUEL
Reference Manuals.  If the value cannot be converted to a valid date,
INGRES will issue an error message.

Let's look at a specific use of the date conversion function.  Suppose a
company has a table containing project names and their due dates. The
due dates are in a field called "complete" with a format of c10. They've
been entered in standard "mm/dd/yy" format. A query to list all due
dates with a 10 day extension is shown below. The resulting column "x"
will be in INGRES' default dd-mmm-yyyy format for dates.

SQL: select name,x=date(projects.complete)+'10 days' from projects;

QUEL: retrieve (,x=date(projects.complete)+"10 days")

5. DOW conversion

The DOW (day of the week) conversion function gives the day of the week
for the provided date value.

For example, an administrative assistant is keeping track of complaints
called in on a hot-line.  He logs each call in an INGRES table which has
a column called 'calldate'.  He wants to see the distribution of calls by
the day of the week:

SQL:	Two steps are required.

1)	CREATE TABLE tempd AS SELECT x=DOW(calls.calldate) FROM calls;
2)	SELECT x,y=COUNT(x) FROM tempd GROUP BY x;

QUEL:	RETRIEVE (x=DOW(calls.calldate),
	y=COUNT(DOW(calls.calldate) BY DOW(calls.calldate)))

6. DATE_TRUNC function

The DATE_TRUNC function returns the first date within a specified unit
of measurement.  For example, if the unit of measurement is "month", then
the DATE_TRUNC function for any date of a particular month will return the
first day of that month as a value.  The syntax is: DATE_TRUNC(UNIT,DATE).

The DATE_TRUNC function is useful for grouping dates.  For example,
suppose a table called "receipts" lists the amount collected in a column
called "amount", and the day it was collected in a column called 
"collected".  You want to know how much was collected each week. The follow-
ing query will show the starting day of the week, i.e. Monday's date,
and the amount collected that week:

SQL:	Two steps are required 

1)	CREATE TABLE temptable AS
	SELECT x=DATE_TRUNC('weeks',receipts.collected),amount
	FROM receipts;

2)	SELECT x,y=SUM(amount) FROM temptable GROUP BY x;

QUEL:	RETRIEVE (x=DATE_TRUNC("weeks",receipts.collected),
	y=SUM(receipts.amount BY

7. DATE_PART function

The DATE_PART function returns an integer that is one part of the date.
The syntax is: DATE_PART(UNIT,DATE).  For example, if the date field has
the value "29-aug-1971", then DATE_PART("day",datefield) returns a value
of 29. For "week" units, the weeks are numbered from 0 to 52.  Week 0 is
assigned to the days before the first Monday of the year.  For "quarter"
units, they are numbered from 1 to 4. The date_part function is useful
when using aggregates.

Suppose an opinion research firm sends the same questionnaire four times
a year to four random samples.  Each set of questionnaires is mailed on
the first of the month. A table called "responses" has the data for all
four surveys.  What is the average number of responses received on each
day of the month?

SQL:  Two steps are required.
      1. create table tempr as
      select idnum,x=date_part('day',responses.received)
      from responses;
      2. select x,y=count(idnum)/4 from tempr group by x;

QUEL: retrieve (a=date_part("day",responses.received),
      by date_part("day",responses.received))/4)

8. INTERVAL function

When you use date arithmetic, like "datefld1  -  datefld2",  the  result
looks  like  "23  days".  Suppose  you  just wanted the number "23"? The
INTERVAL function allows you to obtain the number  of  time  units  con-
tained  within  a  specified interval. The syntax is: INTERVAL(UNIT,TIME
INTERVAL). Allowable units include secs,  mins,  hrs,  days,  wks,  mos,
qtrs, and yrs.

Suppose a date field called  "duration"  has  the  interval  "2  days  5
hours".   Then  INTERVAL("hrs",duration)  returns 53. Note that the date
field can also be an expression whose result is an interval . For  exam-
ple, if a community organization conducting a fund  drive wants to enter
some data along with the number of days since the drive began. The field
daynum is an integer field which will contain the number of days.

SQL:  INSERT INTO drivedata (contribs,daynum)
      VALUES (432.56,INTERVAL('days','today'-date('2/15/87')));

QUEL: APPEND TO drivedata (contribs=432.56,

Combining Functions
The date functions described above can be combined to produce date  cal-
culations  of various kinds. For example, suppose you wanted to find the
midpoint between two dates. Given "1/1/87" and "1/30/87", you'd like  to
have the calculation return a value of "15-jan-1987".

The query is:

SQL:    select x=date('1/1/87')+concat(ascii(int4(interval('days',
        date('1/30/87')-date('1/1/87'))/2)),'days') from dept;

QUEL:   retrieve (x=date("1/1/87")+concat(ascii(int4(interval("days",

Here's what the query does: First, date arithmetic is used  to  subtract
the  two  dates.  The result, "30 days", is converted to the number "30"
using the interval function. The number  is  then  divided  by  2.  That
result  is  converted  to integer format, and then converted to an ascii
string so it can be concatenated to the word "days",  resulting  in  "15
days".  The  "15  days"  is  then added to the starting date, using date
arithmetic, to give the final result.

Consider another example. Using the same "delivery" table described ear-
lier,  suppose  you want to know how many shipments were received on the
first Monday of the month. The query is:

SQL:  Two steps are required --
      1. create table temp as select recvdate,x=dow(recvdate),
      y=date_part('month', recvdate) from delivery;

      2. select z=min(recvdate) from temp
      where x='Mon' group by y;

QUEL: range of d is delivery
      retrieve (d.recvdate)
      where d.recvdate=min(d.recvdate by date_part("month",d.recvdate)
      where dow(d.recvdate)="Mon")

Date arithmetic, date functions and date conversions in INGRES provide a
powerful set of tools for making full use of data in date format.

Releases affected:     all(all.all) -   Releases not affected: 
Ingres Database Reference
To William's Home Page

© William Yuan 2000

Email William