Date - Oracle (Magic xpa 2.x)
The table below lists Magic xpa’s Date attribute with the valid Oracle data types.
Each entry in the table has a default data type that can be forced by specifying that type in the Type property on the SQL section in the Column Properties sheet.
|
Magic xpa Storage Type
|
Magic xpa Picture
|
Magic xpa Storage Size
|
Oracle Data Type
|
|
String Date
|
##/##/####
|
8
|
DATE
|
|
Integer Date
|
##/##/####
|
4
|
NUMBER
|
|
Integer Date 1901
|
##/##/####
|
4
|
NUMBER
|
|
YYMMDD Date
|
##/##/####
|
4
|
RAW
|
|
Magic Date
|
##/##/####
|
4
|
RAW
|
|
Magic Date 1901
|
##/##/####
|
4
|
RAW
|
Note:
-
To map Magic xpa's String Date to Oracle character, specify 'CHAR(8)' as the Type, under the column properties/SQL.
-
To map an Oracle DATE field to a Magic xpa Alpha so that updating, inserting and reading the records will avoid date format related DB errors:
Use an Alpha that is long enough to hold both date and time parts of the database field and to change the Oracle session to match the format.
For example, a Magic xpa Alpha 24 that holds the following string 31/03/2010 16:53:10 should use a matching Oracle format.
Changing the Oracle format accordingly can be done in a direct SQL task called from the main program's Task Prefix with the following statement:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
