Date/Time format strings define how strings are converted into date and time values. These strings consist of specifiers that represent various components of a date or time.
am/pm and a/p, which require the correct casing.These format strings ensure precise parsing and formatting of date and time values, enabling seamless integration into workflows.
| Specifier | Description |
|---|---|
| d | Day as a number without a leading zero (1-31). |
| dd | Day as a number with a leading zero (01-31). |
| ddd | Day as Short Day Name (Sun). |
| dddd | Day as Long Day Name (Sunday). |
| ddddd | Day in Short Date Format (Sun 5) |
| dddddd | Day in Long Date Format (Sunday 5) |
| m | Month as a number without a leading zero (1-12). |
| mm | Month as a number with a leading zero (01-12). |
| mmm | Month as an abbreviation (Jan-Dec). |
| mmmm | Month as a full name (January-December). |
| y | Year as a two-digit number (00-99). |
| yy | Year as a two-digit number (00-99). |
| yyyy | Year as a four-digit number (0000-9999). |
| h | Hour without a leading zero (0-23). |
| hh | Hour with a leading zero (00-23). |
| n | Minute without a leading zero (0-59). |
| nn | Minute with a leading zero (00-59). |
| s | Second without a leading zero (0-59). |
| ss | Second with a leading zero (00-59). |
| fff | Fraction of Second with a leading zero (000-999). (Works only for oracle time stamp fields) |
| tt | Uses the 12-hour clock for the preceding h or hh specifier, ‘am’ for any hour before noon, and ‘pm’ for any hour after noon. |
| ampm | AM/PM |
It is essential to note that the date/time format is unrelated to the target database. Instead, it defines the format of the source data. This format helps the software convert string data into a date or time type, making it suitable for loading into a date or timestamp field later.
16/08/2009 → Format: DD/MM/YYYY1/31/2009 → Format: M/D/YYYY2006-05-23 22:34:42.096 → Format: YYYY-MM-DD HH:NN:SS.FFF1992/mar/12 00:00 → Format: YYYY/MMM/DD HH:NNdd/mm/yyyy is faster than d/m/yy.By selecting appropriate date/time formats, efficiency can be improved while ensuring accurate data conversion.
If the source data is already in the YYYY-MM-DD HH:NN:SS.FFF format, there is no need to apply any additional formatting. This saves processing time and ensures optimal performance, as the data is already in a compatible structure for conversion.
Important: Working with Date and Time fields
For more technologies supported by our ETL Software see Advanced ETL Processor Versions
Confused? Ask question on our ETL Forum