All date-type columns in the Imports Setup tab must have a proper column format. Data column formats are case sensitive. Examples of proper formats are shown below:
- MM/dd/yyyy
- MM-dd-yyyy
- yyyy/MM/dd
- yyyy/MM/dd HH:mm:ss
Date and Time Patterns
Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. All other characters are not interpreted; they are matched against the input string during parsing. See Pattern Letters for the list of pattern letters.
Pattern letters are usually repeated, as their number determines the exact presentation. The following table lists pattern letters for different types of data.
Data Type | Pattern for Formatting | Pattern for Parsing |
|---|---|---|
General Time Zone | Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the following syntax is used (the format is locale independent and digits must be taken from the Basic Latin block of the Unicode standard):
| For parsing, RFC 822 time zones are also accepted. |
Month | If the number of pattern letters is three or more, the month is interpreted as text; otherwise, it is interpreted as a number. |
|
Number | The number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. | The number of pattern letters is ignored unless it's needed to separate two adjacent fields. |
RFC822 Time Zone | For formatting, the RFC822 four-digit time zone format is used:
| For parsing, general time zones are also accepted. |
Text | If the number of pattern letters is four or more, the full form is used; otherwise, a short or abbreviated form is used (if available). | Both forms are accepted — independent of the number of pattern letters. |
Year | If the number of pattern letters is two, the year is truncated to two digits; otherwise it is interpreted as a number. | If the number of pattern letters is more than two, the year is interpreted literally regardless of the number of digits. So using the pattern "MM/dd/yyyy", "01/11/12" parses to Jan 11, 12 A.D. |
Examples
The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time is 2001-07-04 12:08:56 local time in the U.S. Pacific time zone.
Date and Time Pattern | Result |
|---|---|
yyyy.MM.dd G 'at' HH:mm:ss z | 2001.07.04 AD at 12:08:56 PDT |
EEE, MMM d, ''yy | Wed, Jul 4, '01 |
h:mm a | 12:08 PM |
hh 'o''clock' a, zzzz | 12 o'clock PM, Pacific Daylight Time |
K:mm a, z | 0:08 PM, PDT |
yyyyy.MMMMM.dd GGG hh:mm aaa | 02001.July.04 AD 12:08 PM |
EEE, d MMM yyyy HH:mm:ss Z | Wed, 4 Jul 2001 12:08:56 -0700 |
yyMMddHHmmssZ | 010704120856-0700 |
Pattern Letters
Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
a | Am/pm marker | Text | PM |
d | Day in month | Number | 10 |
D | Day in year | Number | 189 |
E | Day in week | Text | Tuesday; Tue |
F | Day of week in month | Number | 2 |
G | Era designator | Text | AD |
h | Hour in am/pm (1-12) | Number | 12 |
H | Hour in day (0-23) | Number | 0 |
k | Hour in day (1-24) | Number | 24 |
K | Hour in am/pm (0-11) | Number | 0 |
m | Minute in hour | Number | 30 |
M | Month in year | Month | July; Jul; 07 |
s | Second in minute | Number | 55 |
S | Millisecond | Number | 978 |
w | Week in year | Number | 27 |
W | Week in month | Number | 2 |
y | Year | Year | 1996; 96 |
z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
Z | Time zone | RFC 822 time zone | -0800 |
