For a description of each of these data types, refer to the appropriate entry in "Supported Data Types".

GLS

The NCHAR and NVARCHAR data types are also character data types.

For information on how to use time data types, see "Using DATE, DATETIME, and INTERVAL Data".

Using DATE, DATETIME, and INTERVAL Data

An INTERVAL column can hold a value that represents the difference between two DATETIME values or the difference between (or sum of) two INTERVAL values. In either case, the result is a span of time, which is an INTERVAL value. On the other hand, if you add or subtract an INTERVAL value from a DATETIME value, another DATETIME value is produced because the result is a specific time.

Figure 2-3 indicates the range of expressions that you can use with DATE, DATETIME, and INTERVAL data, along with the data type that results from each expression.

Figure 2-3
Range of Expressions for DATE, DATETIME, and INTERVAL

(1 of 2)

Data Type of Operand 1	Operator	Data Type of Operand 2	Result
DATE	-	DATETIME	INTERVAL
DATETIME	-	DATE	INTERVAL
DATE	+ or -	INTERVAL	DATETIME
DATETIME	-	DATETIME	INTERVAL
DATETIME	+ or -	INTERVAL	DATETIME
INTERVAL	+	DATETIME	DATETIME
INTERVAL	+ or -	INTERVAL	INTERVAL
DATETIME	-	CURRENT	INTERVAL
CURRENT	-	DATETIME	INTERVAL
INTERVAL	+	CURRENT	DATETIME
CURRENT	+ or -	INTERVAL	DATETIME
DATETIME	+ or -	UNITS	DATETIME
INTERVAL	+ or -	UNITS	INTERVAL
INTERVAL	* or /	NUMBER	INTERVAL

No other combinations are allowed. You cannot add two DATETIME values because this operation does not produce either a specific time or a span of time. For example, you cannot add December 25 and January 1, but you can subtract one from the other to find the time span between them.

Manipulating DATETIME Values

If the second DATETIME value has fewer fields than the first, the shorter value is extended automatically to match the longer one. (See the discussion of the EXTEND function in the Expression segment in Chapter 1 of the Informix Guide to SQL: Syntax.) In the following example, subtracting the DATETIME YEAR TO HOUR value from the DATETIME YEAR TO MINUTE value results in a positive interval value of 60 days, 1 hour, and 30 minutes. Because minutes were not included in the second value, the database server sets the minutes for the result to 0.

DATETIME  (1994-9-30  12:30)  YEAR  TO  MINUTE

	-  DATETIME  (1994-8-1  11)  YEAR  TO  HOUR



Result:  INTERVAL  (60  01:30)  DAY  TO  MINUTE

In the following expression (and result), the year is not included for the second value. Therefore, the year is set automatically to the current year, in this case 1994, and the resulting INTERVAL is negative, indicating that the second date is later than the first.

DATETIME  (1994-9-30)  YEAR  TO  DAY

	-  DATETIME  (10-1)  MONTH  TO  DAY



Result:  INTERVAL  (1)  DAY  TO  DAY [assuming current year 
is 1994]

DATETIME  (1991-8-1)  YEAR  TO  DAY

	+  INTERVAL  (3-5)  YEAR  TO  MONTH



Result:  DATETIME  (1995-01-01)  YEAR  TO  DAY

Important: Evaluate the logic of your addition or subtraction. Remember that months can be 28, 29, 30, or 31 days and that years can be 365 or 366 days.

In most situations, the database server automatically adjusts the calculation when the initial values do not have the same precision. However, in certain situations, you must explicitly adjust the precision of one value to perform the calculation. If the INTERVAL value you are adding or subtracting has fields that are not included in the DATETIME value, you must use the EXTEND function to explicitly extend the field qualifier of the DATETIME value. (For more information on the EXTEND function, see the Expression segment in the Informix Guide to SQL: Syntax.) For example, you cannot subtract a minute INTERVAL value from the DATETIME value in the previous example that has a YEAR TO DAY field qualifier. You can, however, use the EXTEND function to perform this calculation, as shown in the following example:

EXTEND  (DATETIME  (1994-8-1)  YEAR  TO  DAY,  YEAR  TO  MINUTE)

	-  INTERVAL  (720)  MINUTE(3)  TO  MINUTE



Result:  DATETIME  (1994-07-31  12:00)  YEAR  TO  MINUTE

Manipulating DATE with DATETIME and INTERVAL Values

Figure 2-4
Results of Expressions That Manipulate DATE with DATETIME or INTERVAL Values

Expression	Result
DATE - DATETIME	INTERVAL
DATETIME - DATE	INTERVAL
DATE + or - INTERVAL	DATETIME

In the cases that Figure 2-4 shows, DATE values are first converted to their corresponding DATETIME equivalents, and then the expression is computed normally.

Although you can interchange DATE and DATETIME values in many situations, you must indicate whether a value is a DATE or a DATETIME data type. A DATE value can come from the following sources:

• A column or program variable of type DATE
• The TODAY keyword
• The DATE() function
• The MDY function
• A DATE literal

• A column or program variable of type DATETIME
• The CURRENT keyword
• The EXTEND function
• A DATETIME literal

When you represent DATE and DATETIME values as quoted character strings, the fields in the strings must be in proper order. In other words, when a DATE value is expected, the string must be in DATE format and when a DATETIME value is expected, the string must be in DATETIME format. For example, you can use the string '10/30/1994' as a DATE string but not as a DATETIME string. Instead, you must use '1994-10-30' or '94-10-30' as the DATETIME string.

GLS

If you use a non-default locale, the DATE and DATETIME strings must match the formats that your locale defines. For more information, see the Guide to GLS Functionality.

You can customize the DATE format that the database server expects with the DBDATE and GL_DATE environment variables. You can customize the DATETIME format that the database server expects with the DBTIME and GL_DATETIME environment variables. For more information on these environment variables, see the Guide to GLS Functionality.

You also can subtract one DATE value from another DATE value, but the result is a positive or negative INTEGER value rather than an INTERVAL value. If an INTERVAL value is required, you can either convert the INTEGER value into an INTERVAL value or one of the DATE values into a DATETIME value before subtracting.

For example, the following expression uses the DATE() function to convert character string constants to DATE values, calculates their difference, and then uses the UNITS DAY keywords to convert the INTEGER result into an INTERVAL value:

(DATE  ('5/2/1994')  -  DATE  ('4/6/1955'))  UNITS  DAY



Result:  INTERVAL  (12810)  DAY(5)  TO  DAY

EXTEND  (DATE  ('5/2/1994'),  YEAR  TO  MONTH)  -  DATE  ('4/6/1955')



Result:  INTERVAL  (39-01)  YEAR  TO  MONTH

Manipulating INTERVAL Values

INTERVAL  (100:30.0005)  MINUTE(3)  TO  FRACTION(4)

	-  INTERVAL  (120.01)  SECOND(3)  TO  FRACTION



Result:  INTERVAL  (98:29.9905)  MINUTE  TO  FRACTION(4)

When you add or subtract INTERVAL values, the second value cannot have a field with greater precision than the first. The second INTERVAL, however, can have a field of smaller precision than the first. For example, the second INTERVAL can be HOUR TO SECOND when the first is DAY TO HOUR. The additional fields (in this case MINUTE and SECOND) in the second INTERVAL value are ignored in the calculation.

Multiplying or Dividing INTERVAL Values

INTERVAL  (15:30.0002)  MINUTE  TO  FRACTION(4)  *  2.5



Result:  INTERVAL  (38:45.0005)  MINUTE  TO  FRACTION(4)

Large-Object Data Types

Figure 2-5 shows the different large-object data types that Universal Server supports.

Figure 2-5 Large-Object Data Types Supported by Universal Server

For the relative advantages and disadvantages of using simple and smart large objects, see Chapter 9, "Implementing Your Data Model," of the Informix Guide to SQL: Tutorial.

Smart Large Objects

Universal Server supports the following smart-large-object data types:

BLOB	Stores binary data. For more information about this data type, see the description on page 2-33.
CLOB	Stores text data. For more detailed information about this data type, see the description on page 2-38.

Universal Server stores smart large objects in sbspaces. An sbspace is a logical storage area that contains one or more chunks that only store BLOB and CLOB data. For information on how to define sbspaces, see the INFORMIX-Universal Server Administrator's Guide.

When you define a BLOB or CLOB column, you can determine the following large-object characteristics:

• LOG and NOLOG: whether the database server should log the smart large object in accordance with the current database log mode.
• KEEP ACCESS TIME and NO KEEP ACCESS TIME: whether the database server should keep track of the last time the smart large object was accessed.
• HIGH INTEG and MODERATE INTEG: whether the database server should use page headers to detect data corruption.

When you access a smart-large-object column with an SQL statement, the database server does not send the actual BLOB or CLOB data. Instead, it establishes a pointer to the data and returns this pointer. The client application can then use this pointer to perform the open, read, or write operations on the smart large object.

To access a BLOB or CLOB column from within a client application, use one of the following application programming interfaces (APIs):

• From within an INFORMIX-ESQL/C program, use the smart-large-object API.

For more information, see the INFORMIX-ESQL/C Programmer's Manual.

• From within a DataBlade module, use the Client and Server API.

For more information, see the INFORMIX-Universal Server DataBlade API Guide.

For information on using smart large objects, see Chapter 9, "Implementing Your Data Model," of the Informix Guide to SQL: Tutorial and the Data Type and Expression segments in the Informix Guide to SQL: Syntax.

Simple Large Objects

Tip: In INFORMIX-OnLine Dynamic Server, the TEXT and BYTE data types are collectively called "binary large objects" or just "blobs." Universal Server uses the term "simple large object" to refer to TEXT and BYTE to distinguish these data types from the CLOB and BLOB data types.

Unlike smart large objects (see page 2-17), simple large objects do not support random access to the data. When you transfer a simple large object between a client application and the database server, you must transfer the entire BYTE or TEXT value. If the data does not fit into memory, you must store it in an operating-system file and then retrieve it from that file.

Universal Server stores simple large objects in blobspaces. A blobspace is a logical storage area that contains one or more chunks that only store BYTE and TEXT data. For information on how to define blobspaces, see the INFORMIX-Universal Server Administrator's Guide.