Copying Date Field Value

To enable formatting dates based on users' current regional settings when copying date field values, you can use KNTA_USER_UTIL functions in request type SQL defaulting rules:

  • KNTA_USER_UTIL.to_char(user, option, dateObject)

  • KNTA_USER_UTIL.to_date(user, option, dateString)

  • KNTA_USER_UTIL.next_date(user, option, dateString, dateGap)

Following are examples of SQL defaulting rules with SQL-default logic using KNTA_USER_UTIL functions:

  • To copy a value from DATE1 field to DATE2 field.

    DATE1: Date (Long) validation

    DATE2: Date (Long) validation

    Specify SQL defaulting rule as follows:

    Select KNTA_USER_UTIL.to_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]'), '[REQD.VP.DATE1]' from dual

  • To copy a value from DATE1 field to DATE2 field.

    DATE1: Date (Medium) validation

    DATE2: Date (Long) validation

    Specify SQL defaulting rule as follows:

    Select KNTA_USER_UTIL.to_date('[SYS.USERNAME]','MEDIUM','[REQD.VP.DATE1]'), '[REQD.VP.DATE1]' from dual

    Where

    • KNTA_USER_UTIL.to_date('[SYS.USERNAME]','MEDIUM','[REQD.VP.DATE1]') is used to parse a "medium" date string to a date object

    • The VP value in '[REQD.VP.DATE1]' is parsed by the validation. Therefore, there is no need to parse it with an additional SQL statement.

      • Another example:

      DATE1: Date (Long) validation

      DATE2: Date (Short) validation

      Specify SQL defaulting rule as follows:

      Select KNTA_USER_UTIL.to_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]'), '[REQD.VP.DATE1]' from dual

      Where

    • KNTA_USER_UTIL.to_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]') is used to parse a "long" date string to a date object

    • The VP value in '[REQD.VP.DATE1]' is parsed by the validation, and there is no need to parse it with an additional SQL statement.

  • To copy a value from DATE1 field to TEXT1 field

    DATE1: Date validation

    TEXT1: Text Field

    Specify SQL defaulting rule as follows:

    Select '[REQD.VP.DATE1]','[REQD.VP.DATE1]' from dual

  • To copy a value from TEXT1 field to DATE1 field

    DATE1: Date validation

    TEXT1: Text Field

    Depending on the regional settings, the TEXT1 value must be of one of the following user default date formats: LONG, SHORT, or MEDIUM.

    In this case, the LONG format option is used.

    Specify SQL defaulting rule as follows:

    Select KNTA_USER_UTIL.to_date('[SYS.USERNAME]','LONG','[REQD.VP.TEXT1]'), '[REQD.VP.TEXT1]' from dual

  • To copy a value from DATE1 field (with dateGap of +10) to DATE2 field

    DATE1: Date (Long) validation

    DATE2: Date (Long) validation

    Specify SQL defaulting rule as follows:

    Select KNTA_USER_UTIL.next_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]',10),KNTA_USER_UTIL.to_char('[SYS.USERNAME]','LONG', KNTA_USER_UTIL.next_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]',10),) from dual

  • To copy a value from DATE1 field (with dateGap of -10) to DATE2 field

    DATE1: Date Format (Long) validation

    DATE2: Date Format (Long) validation

    Specify SQL defaulting rule as follows:

    Select KNTA_USER_UTIL.next_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]',-10),KNTA_USER_UTIL.to_char('[SYS.USERNAME]','LONG', KNTA_USER_UTIL.next_date('[SYS.USERNAME]','LONG','[REQD.VP.DATE1]',-10),) from dual

Limitations

This feature is subject to the following limitations:

  • It applies to Date Format only. Make sure to set Time Format to None.

  • We strongly recommend using the VP value instead of the P value in SQL defaulting rules. Because the token engine replaces P object value with its toString() results, the P date object becomes a string similar to
    2011-07-12 00:00:00 (JVM determines the format). This unexpected date string from P date object may cause errors.

  • The SQL defaulting rules using KNTA_USER_UTIL functions do not support the following regional settings:

    • English (India)

    • Chinese (Singapore)