Boosting Your e3000 Productivity
Migrating Empty Date Values From TurboIMAGE
where date values are stored in generic data types such as X
(character) or I (integer), SQL databases have a DATE type
specifically for date values.
Any truly invalid date values should be fixed before exporting to another database. You can find the invalid dates easily in Suprtool using the $Invalid function. And while you are looking for invalid dates, you might as well look for unreasonable dates as well (i.e., before 1900 or after 2010, depending upon the field meaning in the application). For example:
Note: you may find
the DBEDIT module of Suprtool handy for correcting the invalid dates
after you find them.
In TurboIMAGE, applications usually initialize SHIPDATE-type fields to Blanks or Zeroes to indicate that the value is not yet know.
In SQL databases, you need to define the column as NULLABLE in order to allow a row to exist without a value in that column. SQL has the concept of a specific NULL value that can exist and be detected (the internal implementation may actually use zeroes or blanks or a special flag, but that is normally not exposed to the user).
The only remaining
question is How do we specify a NULL value in our load data
We did some testing with Microsoft SQL Server 2000 with loading dates. First we created a default table, then we imported the following data from a .txt file:
In the input file ,,
represents the blank date. The rows were inserted into the table
successfully, and the blank dates became NULL values. Just what we
If theres a
question mark in columns 1-10, the LOAD program assumes myfield is a
The same null date load is possible in Oracle as well. Heres the details from the Oracle SQL*Loader Training material at storacle.princeton.edu:9001/oracle8-doc/server.805/a58244/ch05.htm#5290:
NULLIF Keyword: Use
the NULLIF keyword after the datatype and optional delimiter
specification, followed by a condition. The condition has the same
format as that specified for a WHEN clause. The columns value
is set to null if the condition is true. Otherwise, the value remains
The BLANKS keyword
makes it possible to determine easily if a field of unknown length is
blank. For example, use the following clause to load a blank field
You can use Suprtool and STExport to export TurboIMAGE date values in a form that your SQL database will accept. By default, STExport formats invalid dates as asterisks (****). But, there is a special option called Date Invalid Null that converts an invalid date into a zero-length field (you must also specify variable Columns).
For example, first use Suprtool to create a self-describing data file for the dataset:
Then use STExport to read the date file and convert it into a CSV file with invalid dates as null columns:
Note: always set the date
export format to CCYYMMDD, since this is the ISO standard format and
is understood by most SQL load programs without further