This is the detailed pattern syntax for date and time formats that are available for CSV, not Excel:
Symbol Meaning Presentation Examples ------ ------- ------------ ------- G era text AD C century of era (>=0) number 20 Y year of era (>=0) year 1996 x weekyear year 1996 w week of weekyear number 27 e day of week number 2 E day of week text Tuesday; Tue y year year 1996 D day of year number 189 M month of year month July; Jul; 07 d day of month number 10 a halfday of day text PM K hour of halfday (0~11) number 0 h clockhour of halfday (1~12) number 12 H hour of day (0~23) number 0 k clockhour of day (1~24) number 24 m minute of hour number 30 s second of minute number 55 S fraction of second millis 978 z time zone text Pacific Standard Time; PST Z time zone offset/id zone -0800; -08:00; America/Los_Angeles ' escape for text delimiter '' single quote literal '
The most frequently used formats are listed below:
|
Date and time value
|
Pattern
|
|---|---|
| 2013-01-16 | yyyy-MM-dd |
| 16/01/2013 | dd/MM/yyyy |
| 01/16/2013 | MM/dd/yyyy |
| 16 Apr 2013 | dd MMM yyyy |
| 20130116 | yyyyMMdd |
| 14:30:00 | HH:mm:ss |
| 143000 | HHmmss |
| 2013-01-16T14:30 | yyyy-MM-dd'T'HH:mm |
| 2013-01-16 14:30 |
yyyy-MM-dd HH:mm |
| 2024-02-14T11:25:00.000Z | yyyy-MM-dd’T’HH:ss.SSSZ |
The count of pattern letters determine the format.
Text: If the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available.
Number: The minimum number of digits. Shorter numbers are zero-padded to this amount. When parsing, any number of digits are accepted.
Year: Numeric presentation for year and weekyear fields are handled specially. For example, if the count of 'y' is 2, the year will be displayed as the zero-based year of the century, which is two digits.
Month: 3 or over, use text, otherwise use number.
Millis: The exact number of fractional digits. If more millisecond digits are available then specified the number will be truncated, if there are fewer than specified then the number will be zero-padded to the right. When parsing, only the exact number of digits are accepted.
Zone: 'Z' outputs offset without a colon, 'ZZ' outputs the offset with a colon, 'ZZZ' or more outputs the zone id.
Zone names: Time zone names ('z') cannot be parsed.
Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] will be treated as quoted text. For instance, characters like ':', '.', ' ', '#' and '?' will appear in the resulting time text even if they are not embraced within single quotes.
DateTimeFormat is thread-safe and immutable, and the formatters it returns are as well.
With the exception of the formats highlighted in red (which are identified as integers), Duco will recognise all these formats automatically.
However, it is possible to set Duco to recognise e.g. 20130116 as a date rather than an integer in the following way:
- Click on the black arrow next to a field name
- Change the definition of the field from 'Integer' to 'Date/Time'
- The box will extend and prompt you to enter a value into the Field Format
- Enter yyyyMMdd and select the Add button
In a similar way it is possible to set Duco to recognise other date/time format conventions which aren't listed in the table, e.g. Duco will recognise 12:06:56.568+01:00 as a Date and Time format if you follow the same process as above but enter 'HH:mm:ss.SSSZZ' into the Field Format. In addition, if input data contains records with the date in multiple formats in the same field, Duco allows you to set several data formats to respond to this. For example:
*The order in which formats are specified is significant. For example, if you specify the following formats:
dd/MM/yyyy
MM/dd/yyyy
The value "01/02/2014" is interpreted as February 1 2014.