Browse Prior Art Database

Support of Comparisons Between Date/ Time and Character Data in SQL

IP.com Disclosure Number: IPCOM000121674D
Original Publication Date: 1991-Sep-01
Included in the Prior Art Database: 2005-Apr-03
Document File: 3 page(s) / 113K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+3]

Abstract

Disclosed is a method for supporting comparisons between date/time/ timestamp data and character data in an SQL implementation. BACKGROUND

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Support of Comparisons Between Date/ Time and Character Data in SQL

      Disclosed is a method for supporting comparisons between
date/time/ timestamp data and character data in an SQL
implementation.
BACKGROUND

      The OS/2* 1.3+ Extended Edition Database Manager supports
compatibility between date/time/timestamp data types and character
data types.  This is an enhancement in the OS/2 1.3+ Database Manager
product.

      Date/time/timestamp internal data types are stored in a packed
format.  An internal date data type is composed of four bytes,
consisting of two bytes for year value, one byte for month value and
one byte for day value.  An internal time data type is composed of
three bytes, consisting of one byte for hour value, one byte for
minute value and one byte for second value.  An internal timestamp
data type is composed of ten bytes, consisting of two bytes for year
value, one byte for month value, one byte for day value, one byte for
hour value, one byte for minute value, one byte for second value and
three bytes for microsecond value. Character data is stored in its
ASCII form in the Database Manager.

      Part of the date/time/timestamp and character compatibility
enhancement implemented in Database Manager 1.3+ is the support for
comparison between the date/time/timestamp and character data types.
In order to compare data types, the comparands must be of the same
type. Since the date/time/timestamp data types in the Database
Manager are stored in a compacted form as described above, they
cannot be directly compared to character data.  In order to compare
character data to date/time/timestamp data, conversion of the
character data must first be made to the data type of the
date/time/timestamp comparand.  This consists of packing the
character bytes and verifying that the converted value is valid.
Verification includes support for three-character date formats,
three-character time formats and one- character timestamp format.
The character representation of a date may omit leading zeroes from
the month and day portions.  The character representation of a time
may omit seconds and, in some instances, minutes portions.  The
character representation of a timestamp may omit leading zeroes from
the month, day and hour portions and microseconds may be truncated or
entirely omitted.

      One possible implementation is to assume all character data
items are compatible with date/time/timestamp data formats and delay
the determination of character to date/time/timestamp comparisons and
the necessary conversions until execution time of an SQL statement.
This is the straightforward, obvious method.  However, this adds the
overhead of validating and converting the character comparand in
every case where date/time/timestamp and character compatibility is
possible.  In many cases, this would be unnecessary execution-time
overhea...