Dates

OMNIDEX supports date formats in two very distinct ways:

• Native Database Format
• OMNIDEX Index Format

Native database format is how the date data is stored in the native database. This is also how the date column should be declared in the OMNIDEX environment catalog. For example, in an Oracle database, a date is stored as an ORACLE DATETIME, therefore, the column declaration in the OMNIDEX environment catalog should match. This tells OMNIDEX what to expect when retrieving data from the underlying database.

OMNIDEX index format is the format in which the dates are stored in the indexes. This format does not necessarily have to match the native database format. In fact, it should reflect only the information needed to search on. For example, a datetime data type stores the date as well as the time. However, if searches will only be performed on the date portion or even just the year, the OMNIDEX indexes can be defined to store only the relevant portion of the date without altering the underlying data. This speeds searches and decreases storage space for the indexes. This is defined in the FORMAT clause of the column declaration in the OMNIDEX environment catalog.

Date Data Types

OMNIDEX supports the following date data types.

DATE
ANSI date.
datatype DATE

TIME
ANSI time.
datatype TIME

DATETIME
ANSI datetime.
datatype DATETIME

ODBC DATE
datatype ODBC DATE

ODBC TIME
datatype ODBC TIME

ODBC DATETIME
datatype ODBC DATETIME

DB2 DATE
datatype DB2 DATE

DB2 TIME
datatype DB2 TIME

DB2 DATETIME
datatype DB2 DATETIME

INFORMIX DATE
datatype INFORMIX DATE

INFORMIX DATETIME
datatype INFORMIX DATETIME

ORACLE DATETIME
datatype ORACLE DATETIME

ASCII DATE
Length can be 6 or 8 (default).
datatype ASCII DATE(6)
datatype ASCII DATE

C DATETIME
datatype C DATETIME

OMNIDEX DATE
Length can be 2 through 8 (default).
datatype OMNIDEX DATE
datatype OMNIDEX DATE(4) format MMDD

OMNIDEX TIME
Length can be 2 through 8 (default).
datatype OMNIDEX TIME
datatype OMNIDEX TIME(6) format DDHHNN

OMNIDEX DATETIME
Length can be 2 through 16 (default).
datatype OMNIDEX DATETIME
datatype OMNIDEX DATETIME(8) format YYMMDDHH
datatype OMNIDEX DATETIME length 8

Date Formats

The FORMAT clause of the COLUMN section in the Omnidex Environment file applies to Omnidex Date class data types only. This includes OMNIDEX DATE, OMNIDEX DATETIME and OMNIDEX TIME data types.

In the INTERNAL section of the COLUMN section, this refers to the actual data stored in the database, in which case, this is only applicable to flat file databases. This only applies when the data type of the actual data stored in the database is an Omnidex Date class data type, which usually only occurs in flat file databases.
If the data type of the actual data in the database is an Omnidex Date class data type, use this clause to indicate the granularity of the data. For example, if the data type is OMNIDEX DATE and only the year and month are stored in the database, the format_spec would be: YYYYMM or YYMM.

In the OMNIDEX section of the COLUMN section, this refers to what is stored in the index files.
No matter what the data type of the database date data is, the Omnidex indexes store only Omnidex Date class data. Use the FORMAT format_spec clause to define the granularity of the data stored in the index files. For example, if the database data type is ORACLE DATETIME but you only want to search on the date, there is no need to store the time portion in the indexes.

The default format_spec is: YYYYMMDD
This clause DOES NOT affect the appearance of returned data or the data stored in the underlying database.

DISC recommends that you do not index more granularity than is actually needed for the application. For example, in applications that will qualify records based on years, months, or even days, but never on time of day, it is not necessary and not recommended that the time data be indexed. The time data is not lost, it is simply not stored in the Omnidex indexes.

Date formats are based on the following:

YYYYMMDDHHNNSSFF

YYYY
long year - 1941, 2005

YY
short year - 41, 05

MM
month from 01 to 12 - June = 06

DD
day from 01 to 31

HH
hour from 00 to 23
00 for midnight
06 for 6 am
14 for 2 pm

NN
minute from 00 to 59

SS
second from 00 to 59

FF
fraction of a second from 0.01 to 0.99

Date formats can can be any of the following:

YYYY
YYYYMM
YYYYMMDD
YYYYMMDDHH
YYYYMMDDHHNN
YYYYMMDDHHNNSS
YYYYMMDDHHNNSSFF
YY
YYMM
YYMMDD
YYMMDDHH
YYMMDDHHNN
YYMMDDHHNNSS
YYMMDDHHNNSSFF
MM
MMDD
MMDDHH
MMDDHHNN
MMDDHHNNSS
MMDDHHNNSSFF
DD
DDHH
DDHHNN
DDHHNNSS
DDHHNNSSFF
HH
HHNN
HHNNSS
HHNNSSFF
NN
NNSS
NNSSFF
SS
SSFF
FF

Top