Text Connector DateTime Formats
The Fields tab on the Text File As A Source or Text File As A Target Connection dialog has an option to set the data type for the selected field. If you choose DateTime, you must enter the format for the Date/Time in the Date Format field using one of the following formats:
- yyyy-M-d — Example: 2013-6-23
- M/d/yyyy — Example: 6/23/2013
- d/M/yyyy — Example: 23/6/2013
- dd.MM.yyyy — Example: 23.06.2013
- dd/MM/yyyy — Example: 23/06/2013
- yyyy/M/d — Example: 2013/6/23
- yyyy-MM-dd — Example: 2013-06-23
- yyyyMMddTHH:mmzzz — Example: 20130623T13:22-0500
You can enter a format that reflects your data, using any of the following elements.
Date And Time Formats
| Format | Definition |
|---|---|
|
Month |
|
|
M |
Month, from 1 through 12 |
|
MM |
Two digit month, from 01 through 12 |
|
MMM |
Abbreviated name of the month, for example: Jan, Feb, etc. |
|
MMMM |
Full name of the month, for example: January, February, etc. |
|
Day |
|
|
d |
Day of the month from 1 through 31 |
|
dd |
Day of the month from 01 through 31 |
|
ddd |
Standard abbreviation for the day of the week, for example: Mon, Tue, etc. |
|
dddd |
Full name of the day of the week, for example: Monday, Tuesday, etc. |
|
Year |
|
|
y |
Year from 0 to 99 |
|
yy |
Year from 00 to 99 |
|
yyy |
Year with minimum of 3 digits |
|
yyyy |
Year as a four digit number |
|
Hour |
|
|
h |
Hour using 12-hour clock from 1 - 12 |
|
hh |
Hour using 12-hour clock from 01 - 12 |
|
H |
Hour using 24-hour clock from 0 - 24 |
|
HH |
Hour using 24-hour clock from 00 - 24 |
|
Minute |
|
|
m |
Hour using 12-hour clock from 1 - 12 |
|
mm |
Hour using 12-hour clock from 01 - 12 |
|
Second |
|
|
s |
Second, from 0 through 59 |
|
ss |
Second, from 00 through 59 |
|
Other |
|
|
t |
First character of the AM/PM designator |
|
tt |
AM/PM designator |
|
T |
A literal to separate date and time in 8061 or other formats |
|
z |
Hours offset from UTC, with no leading zeros — Example: -5 or +530 |
|
zz |
Hours offset from UTC, with leading zero — Example: -05 or +0530 |
|
zzz |
Hours and minutes offset from UTC, with leading zeros — Example -0500 or +0530 |
|
g or gg |
Period or era, such as A.D. |
|
K |
Zone information |
DateTime Examples
US Standard Format
M/d/yyyy — Example: 6/23/2013
h:mm:ss tt — Example: 5:45:22 AM and 5:45:22 PM
ISO8601 Format
20130623T0545-05000 using the format yyyymmddTHHmmzzz
Russia And Germany Standard Format
dd.MM.yyyy — Example: 23.06.2013
H:mm:ss — Example: 5:45:22 (and 17:45:22)
UK, Spain, France, and Italy standard format
dd/MM/yyyy — Example: 23/06/2013
HH:mm:ss — Example: 05:45:22 and 17:45:22
China And Japan Standard Format
yyyy/M/d — Example: 23/6/2013
Long Format: yyyy年M月dd日 — Example: 2013年6月23日
H:mm:ss — Example: 05:45:22 and 17:45:22
Korea Standard Format
yyyy-MM-dd — Example: 23/6/2013
Long Format: yyyy년 M월 dd일 — Example: 2013년 6월 23일)
tt h:mm:ss — Example: AM 5:45:22 and PM 5:45:22