Datetime Runtime Behavior (Magic xpa 3.x)
The Runtime behavior of accessing Datetime fields from SQL databases is explained below.
If the user inserts the Null value to one part of the Datetime pair and non-Null value to the other part, a runtime error is displayed at the Record Update phase and the record is not written to the database.
A range can be defined for in the Column and Local Variable Range properties or in the End-User range in runtime for the Date part, for the Time part, or for both parts. If the end user selected both parts, the Magic xpa engine consolidates the definitions and produces natural range limitations, which are range limitations on the normal definition of a database of columns. Virtual fields are not included in a natural range. This means that the consolidated range will be different than the current range definition on two separate Date and Time fields.
If only the Date part is selected, Magic xpa produces a normal range limitations. It appends 00:00:00 to the lower date boundary and 23:59:99 to the upper date boundary.
If the end user selected only the Time part of the pair, Magic xpa uses database specific functions to filter the correct records.
If one of the Datetime field’s range or locate value is NULL and the other range or locate value is not a NULL, the Where Clause will be IS NULL.
The Magic xpa SQL Range lets you access the Date and Time parts of Datetime column separately, as Magic xpa does. You are not able to use the Datetime field as a single field through the Magic xpa SQL Range. When you write a reference to a Date field or Time field, which is part of Datetime, Magic xpa uses database-specific functions to retrieve the corresponding records.
In the DB SQL Range, you can write the SQL command that will be sent to the database unchanged. When a reference to either a Date or Time field is made in the DB SQL Range, the name of the field in the database appears without any function.
Sorting is similar to ranges. You can sort both the Date and Time fields, or any part of the pair, in any order. Sorting both fields where the Time field immediately follows the Date field occurs more quickly because Magic xpa uses the database’s natural sort on the Datetime column in the database. Sorting on the Date part of the Datetime field, when this field appears last in the Sort list, also uses a natural sort on the Datetime column in the database.
Note: Unless you specifically require a different sort definition, you should select both Date and Time fields for sorting, as this improves performance.
When using the Link Join tables through a join key, which contains a part of a Datetime pair, you must supply a locate expression for both the Time and Date parts. These expression values are automatically applied according to the key restrictions applied.
When the Datetime fields appear in the Link Join, but not as the join key, and they have locate expressions, if the locate expression are link conditions, such as a reference to another field in the Main source and both fields reference a Datetime pair in the Main Source, only one condition appears in the statement. In other cases, the appropriate functions are added to the Where Clause.
When one part of the Datetime pair is zeroed in the APG Column list, the other part is automatically zeroed. When one part of the Datetime pair is reselected in the APG Column list, the other part is added as the last field in the Field list.
