Date time processing

Functions that process date time items use a customized version of the .NET DateTime data type to enable the handling of incomplete date time fields. In the descriptions of functions, this customized data type is called PFDateTime.

To handle incomplete date time fields (where at least one part of the date time field has the value UNK, for UNKNOWN), date time fields are normalized. Normalization makes sure that UNK date parts have no effect on date computations or comparisons by setting the unknown part of all dates that are involved in the computation to a neutral value. Normalization is performed by all functions that perform date time comparisons or calculations.

During normalization:

• The appropriate date time part value from a template is substituted for each UNK date time part or for a date time field that does not allow entry in all date time parts (for example, a date time field that accepts only dates and no times).

  The default template used in date time functions is 2000-01-01-12:00:00 (Year-Month-Day-Hour-Minute-Second). If you need to perform date time calculations that use a different template for normalization, you can use the _NormalizeDate functions to specify a custom template.

• When using date comparison functions, an additional normalization step occurs to account for the possibility that different date time parts could be marked UNK in the two date time fields. In this case, if a date time part in either date time field has the value UNK, the date time parts in both fields are assigned values from the template.

The following examples use the default template of 2000-01-01-12:00:00.

Examples of date time normalization

Copyright © 2013, 2016 Oracle and/or its affiliates. All rights reserved.