Share this page

# 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)

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: