How Do I Work with DateTime Database Fields in Magic xpi? (Magic xpi 4.13)

Magic xpi does not have native support for the DateTime data type. To SELECT, INSERT, and UPDATE DateTime values to and from the database using the Magic xpi Data Mapper, you need to handle these values as strings.

Magic xpi provides an easy configuration, at the node level, which instructs the Magic xpi Server to handle the DateTime value as a string. The property that controls this behavior is called String Date, and is available for Date data types.

Examples

Databases with a DATETIME Field

To insert Date and Time into a DATETIME data type field, set the String Date property to Yes.

After this modification all Date database fields will be interpreted as a string.

For example, if MSSQL’s DateTime format is 10/01/2007 11:22:41, you can use the following expression in the Calculated value field:

DStr (Date (),'DD/MM/YYYY')&' '&TStr (Time (),'HH:MM:SS')

Oracle Databases

1. Settings

To insert Date and Time into a DATETIME data type field, set the String Date property to Yes.

After this modification all Date database fields will be interpreted as a string.

2. In your Magic xpi project:

Once you have made the relevant changes to the settings (as described above), you need to set the NLS_DATE_FORMAT in Oracle. To be able to insert a DateTime value in Oracle you need to make modifications in two separate Destinations (in the same Data Mapper or in two different Data Mappers).

Destination 1:

1. Drag a Data Mapper utility to the flow area.

2. Double-click or right-click on the Data Mapper and click Configuration to open the Data Mapper window.

3. From the Toolbox's Mapper Schemas section, drag a Database type into the Destination area of the Data Mapper window.

4. In the Properties pane's Dynamic SQL Statement field, select Yes.

5. Click the SQL Statement button and type the following Oracle SQL command:

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS'

This SQL command sets the NLS_DATE_FORMAT of the current Oracle session to: 'YYYY-MM-DD HH:MI:SS'.

6. For the changes to take affect in Magic xpi, click Save.

Destination 2:

1. Drag a Data Mapper utility to the flow area.

2. From the Toolbox's Mapper Schemas section, drag a Database type into the Destination area of the Data Mapper window.

3. In the Properties pane, open the Wizard.

4. In the DB Operation field, select Insert.

5. Select the relevant table and fields using the Magic xpi wizard.

6. Map or directly insert the required data.

7. In the Date field use the exact format you specified in the previous step.
   
   For example: '1980-10-16 18:18:18'