Virtual Data Resources - How to Convert a Date into the Desired Format

While the Virtual Data Resources UI supports configuring a given field as type date, you must use the VDR API to coerce the date format from the provider into the desired format.

First, create a VDR, with at least one field defined/mapped (the date field). For the date field, select the Field settings gear wheel and set the type to date.

Next, from the platform API documentation, get the transformation via API (GET /organizations/elements/{keyOrId}/transformations/{objectName}). Find the date field in the fields array and add or update the field configuration to include a type called transformDate. In the transformDate configuration, the vendor pattern is the date pattern that is returned by the provider, and the pattern is how the date will be returned when calling the VDR.

Note that Cloud Elements uses Java Date Time Format, so both the pattern and vendor pattern will need to use this format.

{
      "type": "date",
      "path": "lastModified",
      "level": "organization",
      "vendorPath": "whenmodified",
      "vendorType": "date",
      "configuration": [
        {
          "type": "passThrough",
          "properties": {
            "toVendor": true,
            "fromVendor": true
          }
        },
        {
          "type": "transformDate",
          "properties": {
            "pattern": "yyyy-MM-dd'T'HH:mm:ss'Z'",
            "vendorPattern": "MM/dd/yyyy HH:mm:ss"
          }
        }
      ]
    }

Finally, update the transformation by calling PUT /organizations/elements/{keyOrId}/transformations/{objectName}.

Note: In the transformDate Configuration, if the date pattern is not consistent from the provider or if we are unaware of the pattern, we can remove the vendorPattern field and add only the pattern in the properties.