Chapter Contents
Previous
Next
|
Chapter Contents |
Previous |
Next |
| SAS/ACCESS Software for Relational Databases: Reference |
| Character Data |
For compatibility with previous versions of CA-OpenIngres, C is supported. C can contain only printing characters; nonprinting characters are converted to blanks. When comparing C strings, blanks are ignored.
TEXT can contain all ASCII characters except NULL characters, which are converted to blanks. When comparing TEXT strings, blanks are significant. TEXT, when comparing strings of unequal length, considers a shorter string "less than" a longer string if all characters up to the length of the shorter string are equal.
| Numeric Data |
The numeric data types for CA-OpenIngres SQL are listed below.
\[+[minus ]\]<digit >\[.[lt ]digit >\]eE\[+[minus ]\]<digit >
where the clauses that are enclosed in the angle brackets (<>) are optional and, if used, can be repeated. The FLOAT4 (4 byte) range is -1.0e+38 to +1.0e+38 with 7-digit precision. The FLOAT (8 byte) range is -1.0e+38 to +1.0e+38 with 16-digit precision.
The default character that indicates the decimal point is a period (.). This character can be changed by setting II_DECIMAL. For more information on setting II_DECIMAL, refer to your CA-OpenIngres installation and operations guide.
Note: REAL is a synonym for FLOAT4.
FLOAT8 and DOUBLE PRECISION are synonyms for FLOAT. ![[cautionend]](../common/images/cautend.gif)
Note: INTEGER2 is a synonym for SMALLINT. INTEGER4 is a synonym
for INTEGER.
| Abstract Data |
CA-OpenIngres SQL date and money data types are abstract data types.
CA-OpenIngres assumes that the year is the current year if the year is omitted. If a two-digit year is used in formats with delimiters such as forward slashes (/) or dashes (-), or if the year is omitted from the date, CA-OpenIngres assumes that the century is 1900.
CA-OpenIngres assumes the current (today's) date if the date is omitted from an absolute time entry.
Dates are stored in Greenwich Mean Time (GMT). The SAS/ACCESS interface to CA-OpenIngres takes care of converting dates. In the following tables, xxx represents a time zone designation. Valid time zone designations are edt, cdt, mdt, pdt, gmt, est, cst, mst, and pst. CA-OpenIngres assumes the local time zone if a time zone designation is not supplied.
At this time, the SAS/ACCESS interface to CA-OpenIngres does not support the CA-OpenIngres date and time interval formats.
The date output format for CA-OpenIngres is FINLAND. You cannot change this format while you are using the SAS/ACCESS engine for Ingres. The FINLAND date output format allows the yyyy-mm-dd input format, plus all of the U.S. input formats except mm-dd-yyyy. (See Input Formats Allowed by FINLAND Date Input Format). For more information, refer to your CA-OpenIngres SQL reference manual and your CA-OpenIngres installation and operations guide.
For information about changing the date format conventions to accommodate international conventions, refer to your CA-OpenIngres SQL reference manual.
| Format | Example | |
|---|---|---|
| 'mm/dd/yyyy' |
'05/16/1991' | |
| 'dd-mmm-yyyy' |
'16-may-1991' | |
| 'yyyy.mm.dd' |
'1991.05.16' | |
| 'yyyy_mm_dd' |
'1991_05_16' | |
| 'yyyy-mm-dd' |
'1991-05-16' | |
| 'mmddyy' |
'051691' | |
| 'mm/dd' |
'05/16' | |
| 'mm-dd' |
'05-16' | |
| 'today' | The string 'today' has today's date as its value. | |
| 'now' | The string 'now' has the current date and time as its value | |
| Format | Example | |
|---|---|---|
| 'hh:mm:ss' |
'10:30:00' | |
| 'hh:mm:ss xxx' |
'10:30:00 est' | |
| 'hh:mm:ss [am|pm]xxx' | '10:30:00 am est' | |
| 'hh:mm' |
'10:30' | |
| 'hh:mm xxx' |
'10:30 est' | |
| Format | Example | |
|---|---|---|
| 'mm/dd/yy hh:mm:ss' |
'05/16/91 10:30:00' | |
| 'dd-mmm-yy hh:mm:ss' |
'16-may-91 10:30:00' | |
| 'mm/dd/yy hh:mm:ss xxx' |
'05/16/91 10:30:00 est' | |
| 'dd-mmm-yy hh:mm:ss xxx' |
'16-may-91 10:30:00 est' | |
| 'dd-mmm-yy hh:mm:ss[am|pm]xxx' | '05-may-91 10:30:00 am est' | |
| 'mm/dd/yy hh:mm' |
'01/16/91 10:30' | |
| 'dd-mmm-yy hh:mm' |
'16-may-91 10:30' | |
| 'mm/dd/yy hh:mm xxx' |
'05/16/91 10:30 est' | |
| 'dd-mmm-yy hh:mm xxx' |
'05-may-91 10:30 est' | |
Money values in CA-OpenIngres are displayed as 20-character strings. The display format is $sdddddddddddd.dd where s is the sign (-for negative and no sign for positive), and d is a digit from 0 to 9.
CA-OpenIngres accepts money values on input as character strings, numbers, valid integers, or floating-point numbers.
For information about the logical names that affect the display of MONEY values, refer to your CA-OpenIngres installation and operations guide.
| CA-OpenIngres NULL and Default Values |
Columns can be defined as NOT NULL so that they cannot contain NULL data. NOT NULL tells CA-OpenIngres not to add a row to the table unless the row has a value for the column.
Columns can be defined so that they cannot contain NULL data. For example, the CREATE TABLE statement for the CUSTOMERS table in Appendix 1 defines the first column, CUSTOMER, as CHAR(8) and NOT NULL. NOT NULL tells CA-OpenIngres not to add a row to the table unless the row has a value for CUSTOMER.
| LIBNAME Statement Data Conversions |
| INGRES Data Type | Default SAS Format | |
|---|---|---|
| CHAR(n) or Cn |
$n. (n < 200) $200. (n > = 200) | |
| VARCHAR(n) or TEXT(n |
$n. (n < 200) $200. (n > = 200) | |
| INTEGER1 | 4.0 | |
| SMALLINT (INTEGER2) | 6.0 | |
| INTEGER (INTEGER4) | 11.0 | |
| FLOAT4 (REAL) | none | |
| FLOAT (FLOAT8) | none | |
| DATE | DATETIME21. | |
| MONEY | DOLLAR19.2 | |
| DATE INTERVAL | NOT SUPPORTED | |
| TIME INTERVAL | NOT SUPPORTED | |
| DECIMAL | m.n (m<= 31 and n<=m) | |
The following table shows the default CA-OpenIngres data types that the LIBNAME statement assigns to SAS variable formats during output operations.
| SAS Variable Format | CA-OpenIngres Data Type | |
|---|---|---|
| $w | CHAR(n) | |
| any date format | DATE | |
| w.0 (w<=2) | INTEGER1 | |
| w.0 (w>2 and w<=4) | SMALLINT | |
| w.0 (where w>4 and w<9) | INTEGER | |
| IBw.d, PIBw.d | INTEGER | |
| all other numeric datatypes | FLOAT | |
| DOLLAR | MONEY | |
| ACCESS Procedure Data Conversions |
| INGRES Data Type | SAS Variable Format | |
|---|---|---|
| CHAR(n) or Cn |
$n. (n < 200) $200. (n > = 200) | |
| VARCHAR(n) or TEXT(n |
$n. (n < 200) $200. (n > = 200) | |
| INTEGER1 | 4.0 | |
| SMALLINT (INTEGER2) | 6.0 | |
| INTEGER (INTEGER4) | 11.0 | |
| FLOAT4 (REAL) | E14.0 | |
| FLOAT (FLOAT8) | E23.0 | |
| DATE | DATETIME21. | |
| MONEY | DOLLAR19.2 | |
| DATE INTERVAL | NOT SUPPORTED | |
| TIME INTERVAL | NOT SUPPORTED | |
| DBLOAD Procedure Data Conversions |
| SAS Variable Format | INGRES Data Type | |
|---|---|---|
| w.0 | INTEGER1 | |
| w.0 | SMALLINT | |
| w.0 | INTEGER | |
| IBw.d, PIBw.d | INTEGER | |
| all other numeric data types | FLOAT | |
| DOLLAR | MONEY | |
| any SAS date, datetime, or time format | DATE | |
| $n | CHAR(n) | |
|
Chapter Contents |
Previous |
Next |
 Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.