DateTime Formatting

Document created by chris_stevens Employee on Oct 19, 2015Last modified by luis_nevarez on Mar 1, 2016
Version 2Show Document
  • View in full screen mode
When moving data from the source to a destination, you need to ensure that the field formatted correctly, not only on the destination end, but also your source. Should your source profile be defined as a character type, you can add a "Date Format" function within your map, which will take your Input Date String and format it based on an Input Mask and Output Mask:
0EM40000000N3OR
Here are the basics of Date formats:
FormatDescriptionExample
dThe day number of the month, from 1 through 31.1, 15, 31
ddThe day number of the month formatted to two digits, from 01 through 31.01, 15, 31
MThe month number of the year, from 1 through 12.1, 8, 12
MMThe month number of the year formatted to two digits, from 01 through 12.01, 08, 12
MMMThe month name, formatted to three letters, from JAN through DEC.JAN, AUG, DEC
yyThe year, formatted to two digits, from 00 through 99.99, 01, 15
yyyyThe year, formatted to four digits, from 0001 through 9999.1999, 2001, 2015

Here are the basics of Time formats:
FormatDescriptionExample
SSSThe millisecond, formatted to three digits, from 000 through 999.005, 135, 865
sThe second, from 0 through 59.1, 30, 59
ssThe second, formatted to two digits, from 00 through 59.01, 30, 59
mThe minute, from 0 through 59.0, 14, 57
mmThe minute, formatted to two digits, from 00 through 59.00, 14, 57
hThe hour, from 1 through 12.1, 8, 12
hhThe hour, formatted to two digits, from 01 through 12.01, 08, 12
HThe hour in 24H time, from 0 through 23.0, 8, 16
HHThe hour in 24H time, formatted to two digits, from 0 through 23.00, 08, 16

Here are some other common formatting attributes:
FormatDescriptionExample
'T'Commonly used to delimit Date and Time.yyyy-MM-dd'T'hh:mm:ss
2015-10-01T12:15:58
ZTimezone offset from UTC-0:00, formatted without colon.hh:mm:ssZ
12:15:58-500
ZZTimezone offset from UTC-0:00, formatted with colon.hh:mm:ssZZ
12:15:58-5:00
'Z'Timezone is UTC-0:00.hh:mm:ss'Z'
12:15:58Z

As for your complete DateTime formats, we're going to use the following date to represent the formatting examples: Thursday, October 1st, 2015 at 03:15:35.775pm (UTC-5:00). These are just some of the common formats:
 
FormatExample
MMddyyyy10012015
MM/dd/yyyy10/01/2015
MM-dd-yyyy10-01-2015
MMddyy100115
yyyyMMdd HHmmss20151001 151535
yyyyMMdd HHmmss.SSS20151001 151535.775
yyyy-MM-dd2015-10-01
yyyy-MM-dd'T'HH:mm:ss2015-10-01T15:15:35
yyyy-MM-dd'T'HH:mm:ssZ2015-10-01T15:15:35-500
yyyy-MM-dd'T'HH:mm:ss.SSS2015-10-01T15:15:35.775
yyyy-MM-dd'T'HH:mm:ss.SSS'Z'2015-10-01T15:15:35.775Z
yyyy-MM-dd'T'HH:mm:ss.SSSZZ2015-10-01T15:15:35.775-5:00

Don't see your format in this list? It's possible to format your own DateTime, simply by typing in your own format into the Date Format box within the Profile.
1 person found this helpful

Attachments

    Outcomes