Date Time Formats in a File Connection

Date and time values read from files using a File Connection in Spectrum Technology Platform need to adhere to date-time formats described here.

Accepted Date Time Formats

  • Date: yyyy-mm-dd"
  • Datetime: yyyy-mm-dd HH:mm:ss
  • Time: HH:mm:ss

Delimited Files

If the Detect type feature is turned on while configuring the Delimited File Connection, then the date and time values in the file records, which adhere to the above formats, are automatically detected as Date type.

If a date-time value does not adhere to one of the accepted formats, the value is read as a String type value instead of a Date type value.

Fixed Width Files

For Fixed Width files, date type values are configured while creating the Fixed Width File Connection. Hence these values are read as Date type values, irrespective of whether they adhere to the accepted formats or not.

If the date-time value in a Fixed Width file does not adhere to the accepted formats, it needs to be handled using Transformations at the logical model creation stage by applying this Conversion category function to the value:
parsedate(String date, String format)
In this, the date is the value received from the file, while the format is the date-time format in which the value is received from the file. This helps to parse the date-time value correctly.
For example, if the date = 23-Feb-2008, then the format = dd-MMM-yyyy.

Resulting Value Formats

While previewing data in a model store:
  • If the value has been read as a date/time value, it is reflected in one of the accepted date/time formats in the preview.
  • If the value has been read as a String value, it is reflected as it is in the preview.