Date formats

Date/Time format strings control the conversion of strings into date time type.

Date/Time format strings are composed from specifiers which describe values to be converted into the date time value.

In the following table, specifiers are given in lower cases. Case is ignored in formats, except for the “am/pm” and “a/p” specifiers.

dDay as a number without a leading zero (1-31).
ddDay as a number with a leading zero (01-31).
mMonth as a number without a leading zero (1-12).
mmMonth as a number with a leading zero (01-12).
mmmMonth as an abbreviation (Jan-Dec).
mmmmMonth as a full name (January-December).
yyYear as a two-digit number (00-99).
yyyyYear as a four-digit number (0000-9999).
hHour without a leading zero (0-23).
hhHour with a leading zero (00-23).
nMinute without a leading zero (0-59).
nnMinute with a leading zero (00-59).
sSecond without a leading zero (0-59).
ssSecond with a leading zero (00-59).
fffFraction of Second with a leading zero (000-999). (Works only for oracle time stamp fileds)
ttUses the 12-hour clock for the preceding h or hh specifier, ‘am’ for any hour before noon, and ‘pm’ for any hour after noon.

Important thing is to understand that this format has nothing to do with your target database. This is the format of the source data. It is there to help to covert string into date time type inside of the software, so it can be loaded later into date or timestamp field

So if source data is:

  • 16/08/2009 than the format is DD/MM/YYYY
  • 1/31/2009 than the format is M/D/YYYY
  • 2006-05-23 22:34:42.096 than the format is YYYY-MM-DD HH:NN:SS.FFF
  • 1992/mar/12 00:00 than the format is YYYY/MMM/DD HH:NN


  • Applying date/time format is a very processor intensive operation,
  • Shorter formats are faster,
  • Formats with no months names are faster as well,
  • dd/mm/yyyy is faster than d/m/yy.

For more technologies supported by our ETL Software see Advanced ETL Processor Versions

Confused? Ask question on our ETL Forum
Last updated: September 17, 2022