Section Navigation

SQL 92 Date and Time - Functional Specification

This specification is incomplete. The following information will be added at a later date:
Specification of interface in ESQL applications.

Description

InterBase customers have expressed the need to manipulate Date information without the inconvenience of manipulation of Time information. Customers have also expressed the need for a richer set of manipulation operations on Date & Time values.

In InterBase V6, we are addressing those needs as follows:

Separation of the existing Date+Time data type into Date, Time, & Date+Time data types.
Provide manipulation functions to extract component parts of Date or Time information.
Additional ways to query the system for current date & time information.

Additionally, we are using this opportunity to improve data storage requirements as follows:

Columns requiring only Date or only Time information will take 4 bytes instead of 8.

We are increasing our conformance with SQL by doing the following:

Implementation of Date & Time support per SQL 92 standard
Migration of existing DATE keyword to SQL 92 Semantics
Addition of EXTRACT(), CURRENT_DATE, CURRENT_TIME, & CURRENT_TIMESTAMP functions.

InterBase History:

Previous to V3 of InterBase, GDML defined a "date" data type. This type represented date and time information in an 8-byte quantity. Appropriate semantics were defined for date operations (comparison, subtraction, and conversion from literal). Special conversions were provided from the strings "TODAY", "TOMORROW", "YESTERDAY", and "NOW".

In InterBase V4.0, the GDML date type was supported in SQL as a DATE type, representing the same data type as GDML. Literal conversion was provided through the CAST(… AS DATE) operator. This data type has remained as the only InterBase "DATE" type data type.

OSRI manual documents the range for DATE values as 1 January 100 to 11 December 5941.

The actual range for DATE values is somewhat larger, but I found that isc_decode_date() has problems with dates prior to 31-Jan-0000.

SQL History:

SQL 92 defined a rich set of data types for date & time information.
(NOTE: This section is documenting SQL 92, not what will be in InterBase V6.0)

DATE	Date type in format YYYY-MM-DD
TIME	Time value in format HH:MM.SS.MMMM Seconds precision is implementation defined.
TIMESTAMP	Date & Time value YYYY-MM-DD HH:MM:SS.MMMM Seconds precision is implementation defined.
TIME(p) TIMESTAMP(p)	Time or Timestamp value with seconds precision set to p decimal digits.
TIME(p) WITH TIME ZONE	TIME with a time zone specification in format: "SHH:MM", where S represents the sign (+ or -)
TIMESTAMP(p) WITH TIME ZONE	TIMESTAMP with time zone specification.
INTERVAL iStart TO iEnd	IStart & iEnd are drawn from the set {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND} e.g.: INTERVAL DAY TO SECOND

SQL 92 requires a DATE column to store YEAR, MONTH, and DAY.
SQL 92 requires a TIME column to store HOUR, MINUTE, and SECOND.
SQL 92 requires a TIMESTAMP column to store YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

Time/Date Part	SQL Defined Range of Valid values
YEAR	-9999 to 9999 (Note: this needs to be checked)
MONTH	1 - 12
DAY	1 - 31
HOUR	0 - 23 Note: 0:00 is midnight, 12:00 is Noon. 24:00 is not a valid time.
MINUTE	0 - 59
SECOND(p)	0 - 59.9999 Maximum number of Decimal digits is implementation defined. SQL makes provision for the support of Leap Seconds (values 60 & 61) which are occasionally added by the scientific community to account for variations in the Earth's rotational speed. Optional (p) parameter determines the minimal precision for SECONDS.

Combinations of time/date parts must be legal. For instance:

1998-6-31	Not a legal date. June has 30 days.
1998-2-29	Not a legal date. 1998 is not a Leap Year
1996-2-29	Legal - 1996 was a leap year
1900-2-29	Not legal. 1900 was not a leap year.
1582-10-15	Not legal. When England switched to the Gregorian Calendar from the Julian one in 1582, several days were skipped. This IS a legal date in the calendar of other countries.

Additional information may be stored, but cannot be significant for comparison, etc. (For instance, a SQL Vendor could store the day-of-week of a particular date, but cannot use that information when comparing two dates. Or, a vendor could store TIME information with a DATE column, but must not make use of that information when comparing two DATE values).
Reference: SQL Oct 97, pg. 30, section 4.8

Additionally, SQL 92 defined a set of DATE & TIME Manipulations

DATE - DATE	INTERVAL (range specified by application)
DATE + INTERVAL	DATE
TIME - TIME	INTERVAL (range specified by application)
TIME + INTERVAL	TIME
DATE + TIME	TIMESTAMP

Extraction from a date or time value is also defined in SQL 92
EXTRACT (<part> FROM <datetype>)
Where <part> is one of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

For instance:

SELECT name FROM operations WHERE EXTRACT(HOUR FROM start_time) < 7;

Extract Field	InterBase data type	Result on input "1998-3-24 4:21:1.2345"
YEAR	SMALLINT	1998
MONTH	SMALLINT	3
DAY	SMALLINT	24
HOUR	SMALLINT	4
MINUTE	SMALLINT	21
SECOND	DECIMAL (6,4)	1.2345

Reference: SQL, Oct 97, Section 6.14, page 155

Predefined Functional Operators:

CURRENT_TIME	Time of day at moment of evaluation
CURRENT_DATE	Date at moment of evaluation
CURRENT_TIMESTAMP	Date & Time at moment of evaluation

Reference: SQL, Oct 97, pg. 171, section 6.16

Special Note: General Rule 3 requires that CURRENT_TIME return the same value for multiple evaluations within the SQL statement. Eg:

SELECT … WHERE CURRENT_TIME=CURRENT_TIME;

The equality operator always returns TRUE. Any statements triggered by a SQL statement (eg: Triggers or Stored Procedures) will evaluate CURRENT_TIME to be the same value. This value persists until the end of the SQL statement.

Literal values for Dates, Times & intervals are expressed by preceding their string representation with the appropriate data type keyword.

Literal	Meaning
DATE '1998-3-24'	March 24, 1998
TIME '04:21:23.456'	4:21 am
TIMESTAMP '1998-3-24 04:21:23.456'	4:21:23.456 am on March 24, 1998
INTERVAL '+40-0' YEAR TO MONTH	40 Years and 0 months.
INTERVAL '-5:30:00' HOUR TO MINUTE	5 Hours and 30 minutes previously (negative interval)
TIME '12:13 ' AT TIME ZONE '-08:00'	12:13 PM, Pacific Standard Time

Reference: SQL, Oct 97, section 5.3, pg. 102

SQL also defines an OVERLAPS operator, as in:

SELECT * WHERE start + duration OVERLAPS race_finish;

For InterBase V6.0 - we are supporting the following features from SQL 92:

TIMESTAMP
TIME
DATE -- Existing DATE support will change.
EXTRACT()
Literal TIME, DATE, TIMESTAMP
CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

We will NOT be supporting

TIME ZONE data-types
INTERVAL data-types
TIME(p) or TIMESTAMP(p) -- All TIME & TIMESTAMP columns will be defined as precision 4.
OVERLAPS

Note: Our existing InterBase V5 treatment of Date & Time interval operations is roughly the same as the SQL defined INTERVAL DAY TO SECONDS(4).

Operation Data-types

The Addition (+) and Subtraction (-) operators are available in expressions involving dates.

In the following tables, "Numeric Value" means any value that can be cast to a exact numeric value by the engine. (e.g.: double, float, integer, short, decimal, numeric, "string with numeric only") - or an expression resulting in a "Numeric Value"

Operator +

Operand1	Operand2	Result
DATE	DATE	Error
DATE	TIME	TIMESTAMP	(Essentially a concatenation)
DATE	TIMESTAMP	Error
DATE	Numeric Value	DATE	DATE + Number of Days (fractional part ignored)
TIME	DATE	TIMESTAMP	Concatenation
TIME	TIME	Error
TIME	TIMESTAMP	Error
TIME	Numeric Value	TIME	Time + Number of Seconds (24 hour modulo arithmetic)
TIMESTAMP	DATE	Error
TIMESTAMP	TIME	Error
TIMESTAMP	TIMESTAMP	Error
TIMESTAMP	Numeric Value	TIMESTAMP	DATE+Number of Days, TIME + Fraction of Day (converted to seconds) with Carry into Date part.
Numeric Value	DATE	DATE	Symmetric to DATE+Numeric
Numeric Value	TIME	TIME	Symmetric to TIME+Numeric
Numeric Value	TIMESTAMP	TIMESTAMP	Symmetric to TIMESTAMP+Numeric
Numeric Value	Numeric Value	Numeric Value	Not a date type value

Operator -

Operand1	Operand2	Result
DATE	DATE	DECIMAL(9,0)	representing # of Days
DATE	TIME	Error
DATE	TIMESTAMP	Error
DATE	Numeric Value	DATE	Same as DATE + <negative numeric>
TIME	DATE	Error
TIME	TIME	DECIMAL(9,4)	DECIMAL(9,4) representing # of seconds.
TIME	TIMESTAMP	Error
TIME	Numeric Value	TIME	Same as TIME + <negative numeric>
TIMESTAMP	DATE	Error
TIMESTAMP	TIME	Error
TIMESTAMP	TIMESTAMP	Numeric Value	DECIMAL(18,9) representing Days + Fraction of day
TIMESTAMP	Numeric Value	TIMESTAMP	Same as TIMESTAMP + <negative numeric>
Numeric Value	DATE	Error
Numeric Value	TIME	Error
Numeric Value	TIMESTAMP	Error
Numeric Value	Numeric Value	Numeric Value	Not a date type value.

DATE, TIME, & TIMESTAMP operations can occur in some aggregate contexts:

MAX	(DATE) or (TIME) or (TIMESTAMP)	Ok
MIN		Ok
Group By		Ok
Count		Ok
DISTINCT		Ok
SUM		Error
AVERAGE		Error

CAST Operator

The datetime datatypes interact with other SQL datatypes per the CAST operator.

Casting FROM datatype to datetime type:

CAST ( <below type> TO <right type> )	TIMESTAMP	DATE	TIME
SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION NUMERIC DECIMAL	Error	Error	Error
VARCHAR(n) CHARACTER(n) CSTRING(n)	Succeeds if string in format YYYY-MM-DD HH:MM:SS.thou	Succeeds if string in format YYYY-MM-DD	Succeeds if string in format HH:MM:SS.thou
BLOB	Error	Error	Error
TIMESTAMP	Always succeeds	Date portion of TimeStamp	Time portion of TimeStamp
DATE	Always succeeds. Time portion of timestamp set to 0:0:0.0000	Always succeeds	Error
TIME	Always succeeds. Date portion of Timestamp set to base-0 date (November 17, 1858)	Error	Always succeeds

CAST FROM Datetime type to other SQL datatypes

CAST (<right type> TO <below type>)	TIMESTAMP	DATE	TIME
SMALLINT INTEGER FLOAT DOUBLE PRECISION NUMERIC DECIMAL	Error	Error	Error
VARCHAR(n) CHARACTER(n) CSTRING(n)	Succeeds if n is 24 characters or more. Resulting string is in format: YYYY-MM-DD HH:MM:SS.thou	Succeeds if n is 10 characters or more. Resulting string is in format: YYYY-MM-DD	Succeeds if n is 13 characters or more. Resulting string is in format: HH:MM:SS.thou
BLOB	Error	Error	Error
TIMESTAMP	Always succeeds	Succeeds, time portion set to 0:0:0.0000	Succeeds, date portion set to 17 November 1858
DATE	Succeeds, date portion of timestamp is the result.	Always succeeds.	Error
TIME	Succeeds, time portion of timestamp is the result.	Error	Always succeeds

User interface/Usability

SQL Syntax (Both DSQL & ESQL)

Data Type

<v6_datatype>::=	<v5 data type>	\|\|
	TIME	\|\|
	SQL DATE	\|\|
	TIMESTAMP	;

Note: The semantic meaning of the InterBase V5 Data type DATE changes from equivalent to InterBase V6 TIMESTAMP to equivalent to InterBase V6 SQL DATE depending on the SQL Dialog being used by the customer.

Literal Values:

<v6_literal>::=	<v5 literal>	\|\|
	TIME 'hh:mm:ss.ffff'	\|\|
	DATE 'yyyy-mm-dd'	\|\|
	TIMESTAMP 'yyyy-mm-dd hh:mm:ss.ffff'	;

Data Values

<v6_value>::=	<v5 value>	\|\|
	<extract_expression>	\|\|
	CURRENT_TIME	\|\|
	CURRENT_DATE	\|\|
	CURRENT_TIMESTAMP	;

<extract expression>	EXTRACT ( <extract part> FROM <value> )	;

<extract part>	YEAR	\|\|
	MONTH	\|\|
	DAY	\|\|
	HOUR	\|\|
	MINUTE	\|\|
	SECOND	\|\|
	WEEKDAY	\|\|
	YEARDAY	;

InterBase extension to SQL.

Notes: CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP return values based on the moment of execution. For a single SQL statement the same value is used for each evaluation of CURRENT_TIME/DATE/TIMESTAMP within that statement. This means that if multiple rows are updated with:

UPDATE aTable SET aTime=CURRENT_TIME;

Each data row will have the same value in the aTime column. Similarly, if row buffering is occurring in a fetch via the Remote Protocol, the CURRENT_TIME is based on the time of OPEN of the cursor from the engine, not the time of delivery to the client.

CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP use the server's clock & server's time zone.

Data Types of expressions:

The data type of CURRENT_* type expressions is as follows:

CURRENT_DATE	DATE (a.k.a. SQL DATE)
CURRENT_TIME	TIME
CURRENT_TIMESTAMP	TIMESTAMP

The data type of EXTRACT() expressions depend on the specific part being extracted:

Extract Part	Resulting Data type	Representing
YEAR	SMALLINT	Year of value, range 0 … 5400
MONTH	SMALLINT	Month of value, range 1 .. 12
DAY	SMALLINT	Day within month, range 1 .. 31
HOUR	SMALLINT	Hour, range 0 .. 23
MINUTE	SMALLINT	Minute, range 0..59
SECOND	DECIMAL(6,4)	Second, range 0 .. 59.9999
WEEKDAY	SMALLINT	Day of week, range 0 .. 6, (for SUNDAY, MONDAY … SATURDAY)
YEARDAY	SMALLINT	Day of year, range 1 .. 366.

The <value> passed to EXTRACT must be a TIME, TIMESTAMP, or DATE type.

EXTRACT of a part that doesn't exist in the <value> data-type results in an error. (For example, EXTRACT (YEAR FROM aTime) or EXTRACT (HOURS FROM aDate))

Input value to EXTRACT	Extract Part		Result
DATE	YEAR, MONTH, DAY, WEEKDAY, YEARDAY		OK
	HOUR, MINUTE, SECOND		Error
TIME	YEAR, MONTH, DAY, WEEKDAY, YEARDAY		Error
	HOUR, MINUTE, SECOND		OK
TIMESTAMP	Any	OK
Other	Any	Error

DEFAULT CLAUSES

A Column or Domain definition may specify a DEFAULT clause. The default expression can be one of the SQL <datetime value functions>, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.

The evaluation of the <datetime value function> occurs at the time the value is called for, and follows the SQL rules for all evaluations of such functions in a single statement to return the same result.

Default Values:

<v6_default>::=	<v5 default>	\|\|
	DEFAULT CURRENT_TIME	\|\|
	DEFAULT CURRENT_TIMESTAMP	\|\|
	DEFAULT CURRENT_DATE	;

DSQL & ESQL

New SQL Data types are returned in the SQLDA to indicate a v6 DATE or TIME data value.

Data Type	#define	Size	Alignment
DATE	SQL_ANSI_DATE	4 bytes	4 bytes
TIME	SQL_TIME	4 bytes	4 bytes
TIMESTAMP	SQL_DATE (historical) SQL_TIMESTAMP	8 bytes	4 bytes

New BLR

Blr_timestamp		Alias for existing blr_date
Blr_sql_date		Data definition
Blr_sql_time		Data definition
Blr_extract <extract_part> <value expression>
Blr_extract_year Blr_extract_month Blr_extract_day Blr_extract_hour Blr_extract_minute Blr_extract_second Blr_extract_weekday Blr_extract_yearday	Sub-parameter to blr_extract
Blr_literal can now have a blr_sql_time, blr_sql_date, or blr_timestamp type
Blr_current_date		Returns current date as DATE type
Blr_current_time		Returns current time as TIME type
Blr_current_timestamp		Returns current date & time as TIMESTAMP

Metadata representation:

Source data type	RDB$FIELD_TYPE	RDB$FIELD_LENGTH	BLR
(v5 & v6) GDML Date	Blr_timestamp	8	blr_timestamp
(v5) DATE	Blr_timestamp	8	blr_timestamp
TIMESTAMP	Blr_timestamp	8	blr_timestamp
(v6) DATE SQL DATE	blr_sql_date	4	blr_sql_date
TIME	blr_sql_time	4	blr_sql_time

Error Conditions:

Improper part to EXTRACT

EXTRACT (Blood from aStone)

SQL Syntax error

Improper data type input to extract

EXTRACT (HOUR from IntegerValue)

EXTRACT(HOUR from DATE)

EXTRACT (YEAR from TIME)

Presentation to DSQL API - to be written

In InterBase V5, a TIMESTAMP value was presented to a DSQL client as

sqltype=SQL_DATE

In InterBase V6, Types are presented to DSQL as follows

DATATYPE	sqltype
TIMESTAMP	SQL_TYPE_TIMESTAMP (aka SQL_DATE)
DATE	SQL_TYPE_DATE
TIME	SQL_TYPE_TIME

Note: existing SQL_DATE is renamed to SQL_TIMESTAMP, but NOT renumbered.

Reference: ANSI/ISO SQL CLI interface specification, October 1997, page 85 (Table 50)
Page 132 (Section 6.15 - DescribeCol)
Page 328 (Annex A.1 - C Header File SQLCLI.H)

Requirements and Constraints

General InterBase requirements apply.

The requirements of this project:

This new feature will not break existing applications (ISQL nor GPRE)
Provide greatest flexibility to customers to mix the new feature with the current functionality within one application
Customer should not make any changes to existing applications when migrating to the new feature.

Migration Issues

The new date data types (SQL DATE, TIMESTAMP, and TIME) present no migration problems for existing applications.

Data in pre-existing InterBase V5 DATE columns will not need to be migrated. In InterBase V6, the column will appear as a TIMESTAMP column automatically.

The primary migration problem will exist in the source of application programs that make use of the V5 DATE type. In InterBase V6 the DATE keyword represents a date-only data type, in InterBase V5 DATE represented a date+time data type.

An additional migration issue is the conversion of TIMESTAMP information to/from CHARACTER types is different - and may lead to subtle problems in customer applications.

Affected applications are:

Embedded SQL programs that are recompiled under InterBase V6 GPRE
Dynamic SQL
Dynamic SQL scripts. (Such as ISQL scripts)
Stored procedures that are modified under InterBase V6.
UDF's which accept InterBase V5 DATE parameters or return InterBase V5 DATE
Calculated fields which use the InterBase V5 DATE (eg: … CAST(expression AS DATE) …)
Views which calculate values using InterBase V5 DATE.

In InterBase V5, Date Arithmetic was performed using double floating point numbers. In InterBase V6 exact numeric calculation is used. Differences, which may be noticed by application programs, are:

Rounding errors when dealing with fractions of a day are virtually eliminated.
The result of <timestamp> - <timestamp> is DECIMAL(18,9) in InterBase V6, instead of double precision as in InterBase V5.
DATE and TIMESTAMP arithmetic is done by adding DAYS to a DATE and DAYS.FRACTION_OF_DAY to a TIMESTAMP.
TIME arithmetic is done by adding SECONDS to a TIME

In all of the above, the source program is only impacted if it makes use of the DATE keyword, and expects DATE to represent a Date+Time value. It's possible that hidden bugs are actually fixed by the migration should the application designer expect that InterBase V5 DATE represented a Date-only value.

The following table illustrates differences in behavior between InterBase V5 DATE, InterBase V6 DATE and ANSI SQL92 DATE.

Operation

ANSI SQL 92

Create Date type Field

Fname DATE

Meaning Fname can hold a Date & Time value (TIMESTAMP)

Fname DATE

Fname can hold Date value only.

Fname DATE

Fname can hold Date value only.

Date Literal

Not supported. Use CAST ('3-24-1958' AS DATE)

DATE '1958-3-24'

Different separators will be allowed. 2-digit year not allowed.

DATE '1958-3-24'

Only - is allowed as separator.

Date to String

CAST (fname AS CHARACTER(30))

Result format:

DD-Mon-YYYY HH:mm:SS.Hund

Mon is a 3-letter English month abbreviation.

Note: the TIME portion of fname would also appear in the string.

The result string is silently truncated to fit in the given string type.

CAST (fname AS CHARACTER(30))

Result format:

YYYY-MM-DD

MM is a two-digit month.

Inability to fit in the given string variable results in a string truncation exception

CAST (fname AS CHARACTER(30))

Result format

YYYY-MM-DD

Note: This may not actually be required by the SQL specification, but it is logical given the format of a literal.

Inability to fit in the given string variable results in a string truncation exception.

String to Date

CAST (string AS DATE)

Input format is:

MM-DD-YYYY HH:mm:SS.Hun or

DD.MM:YYYY HH:mm:SS.Hund

(/ - , : . can be used as separators)

Years in the range 00-99 will be interpreted as within 100 years of current year.

CAST (string AS DATE)

The format:

YYYY-MM-DD is supported.

Other unambiguous formats are also supported.

(e.g.: YYYY-Mon-DD, but not YYYY-DD-MM).

2 digit years are not allowed in YYYY-MM-DD format.

CAST (string AS DATE)

The format:

YYYY-MM-DD

Must be supported. Other formats may be supported provided there is no ambiguity with the SQL format.

2 digit years are treated as 00yy.

Obtain Current Date

CAST ("TODAY" AS DATE)

CURRENT_DATE

Obtain Current Time

Not really supported. CAST ("NOW" AS DATE) - and then ignore the date portion

CURRENT_TIME

(Not supported for v6 DATE)

CURRENT_TIME

(Not supported for SQL DATE)

Obtain Current timestamp

CAST ("NOW" AS DATE)

CURRENT_TIMESTAMP

(Not supported for v6 DATE column)

CURRENT_TIMESTAMP

(Not supported for SQL DATE)

Arithmetic

DATE - DATE

Date1 - Date2==

Result type is double precision, representing # of days, with a fractional part.

Date1 - Date2==

Result is DECIMAL(15,0), representing number of days.

(Date1 - Date2) DAYS TO SECONDS

Result is an INTERVAL type, which does not behave like a float

DATE +/- numeric

Numeric +/- DATE

Date1 + NumericValue

Numeric interpreted as Days + fractional days

Date1 + NumericValue

Numeric interpreted as Days (fractional part ignored)

Date1 + IntervalValue

The interval determines the interpretation of internal value.

TIMESTAMP - TIMESTAMP

No Timestamp type available.

Ts1 - Ts2==

Result is DECIMAL(18,9) representing number of days and fraction of day.

Ts1 - Ts2==

Result is an INTERVAL.

TIMESTAMP +/- Numeric

Numeric +/- TIMESTAMP

No timestamp type available.

Comparison

Date1=Date2

As date values are really TIMESTAMP, this can return FALSE even if both values are on the same day.

Date1=Date2

Columns represent Dates only, so no fractional time part is included in the comparison.

Date1=Date2

Only the day portion is significant for the comparison.

Conversion

CAST(DATE AS …)

DATE, CHAR(n), VARYING(n)

GDML may have been more lenient.

CAST(DATE AS …)

DATE, CHAR(n), VARYING(n), TIMESTAMP

CAST(DATE AS …)

DATE, CHAR(n), VARYING(n), TIMESTAMP

Client=InterBase V5.0

Server	ODS	Behavior
IB 5.0	ODS 8.x or 9.0	No change from existing V5 support
IB 5.0	ODS 10.x	Not possible.
IB 6.0	ODS 8.x or 9.0	DSQL: EXTRACT available, CURRENT_DATE (etc) available. DSQL: Literal DATE, TIME, & TIMESTAMP available. TIMESTAMP available. DATE available in Dialect 1 only. SQL DATE & TIME unavailable ESQL: programs behave as V5
IB 6.0	ODS 10.x	ISQL Show & Extract will fail for V6 DATE & TIME columns. DSQL: DATE & TIME values returned via DSQL will be transliterated to TIMESTAMP. DSQL: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP available DSQL: EXTRACT available. DSQL: Literal DATE, TIME, & TIMESTAMP available DSQL Client can define new SQL DATE, TIME, & TIMESTAMP columns. ESQL: programs behave as V5.

Client=InterBase V6.0

IB 5.0	ODS 8.x or 9.0	No change from existing V5 support
IB 5.0	ODS 10.x	Not possible.
IB 6.0	ODS 8.x or 9.0	SQL DATE & TIME columns unavailable
IB 6.0	ODS 10.x	All functions available in ESQL & DSQL.

Summary of migration matrix:

InterBase V6 DATE & TIME columns can only be defined in an ODS 10.x Database.

An InterBase V5 Client accessing a DATE or TIME value will have it changed into a TIMESTAMP by the remote protocol.

Reference Documents

ANSI SQL 3 Draft Specification, Oct 1997. (Note: DATE & TIME support was finalized in SQL 92, the SQL 3 specification introduces some clarifications).

ANSI SQL 3: Part 3 Call-Level Interface (SQL/CLI)

Sign In

Section Navigation

SQL 92 Date and Time - Functional Specification

Description

InterBase History:

SQL History:

User interface/Usability

Requirements and Constraints

Migration Issues

Reference Documents