When configuring an delimited input for a file data source, if you are using a Date or a Timestamp type for a column, you will need to specify an array of date formats. The configuration for this will look something like this:

columns:
- name: effdt
  type: Timestamp
  dateFormat:
  - yyyy-MM-dd HH:mm:ss
  - yyyy-MM-dd hh:mm:ss tt

This will configure the column effdt to be parsed as a Timestamp data type, using one of the string formats of "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd hh:mm:ss tt".

If you are receiving failures due null or empty date values and you would like the loader to ignore the failures and instead insert a default value for the date or timestamp, you can change the above configuration to look like:

columns:
- name: effdt
  type: Timestamp
  dateFormat:
  - yyyy-MM-dd HH:mm:ss
  - yyyy-MM-dd hh:mm:ss tt
  defaultValue: 1900-01-01 00:00:00


Date Format Options
Below are all of the different options for use in the dateFormat array item, the different items can be used to construct a format string and used as in the examples above.

"d"
- The day of the month, from 1 through 31.
"dd" - The day of the month, from 01 through 31.
"ddd" - The abbreviated name of the day of the week.
"dddd" - The full name of the day of the week.
"f" - The tenths of a second in a date and time value.
"ff" - The hundredths of a second in a date and time value.
"fff" - The milliseconds in a date and time value.
"ffff" - The ten thousandths of a second in a date and time value.
"fffff" - The hundred thousandths of a second in a date and time value.
"ffffff" - The millionths of a second in a date and time value.
"fffffff" - The ten millionths of a second in a date and time value.
"F" - If non-zero, the tenths of a second in a date and time value.
"FF" - If non-zero, the hundredths of a second in a date and time value.
"FFF" - If non-zero, the milliseconds in a date and time value.
"FFFF" - If non-zero, the ten thousandths of a second in a date and time value.
"FFFFF" - If non-zero, the hundred thousandths of a second in a date and time value.
"FFFFFF" - If non-zero, the millionths of a second in a date and time value.
"FFFFFFF" - If non-zero, the ten millionths of a second in a date and time value.
"g", "gg" - The period or era.
"h" - The hour, using a 12-hour clock from 1 to 12.
"hh" - The hour, using a 12-hour clock from 01 to 12.
"H" - The hour, using a 24-hour clock from 0 to 23.
"HH" - The hour, using a 24-hour clock from 00 to 23.
"K" - Time zone information.
"m" - The minute, from 0 through 59.
"mm" - The minute, from 00 through 59.
"M" - The month, from 1 through 12.
"MM" - The month, from 01 through 12.
"MMM" - The abbreviated name of the month.
"MMMM" - The full name of the month.
"s" - The second, from 0 through 59.
"ss" - The second, from 00 through 59.
"t" - The first character of the AM/PM designator.
"tt" - The AM/PM designator.
"y" - The year, from 0 to 99.
"yy" - The year, from 00 to 99.
"yyy" - The year, with a minimum of three digits.
"yyyy" - The year as a four-digit number.
"yyyyy" - The year as a five-digit number.
"z" - Hours offset from UTC, with no leading zeros.
"zz" - Hours offset from UTC, with a leading zero for a single-digit value.
"zzz" - Hours and minutes offset from UTC.

Did this answer your question?