Date/Time Formatting

Document created by chris_stevens Employee on Oct 19, 2015Last modified by thanh_n88 on Jul 5, 2018
Version 3Show 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
For the full list of date/time formatting and other syntax, view the help documentation Date/Time datatype
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 date/time 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:
 
FormatResult
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 date/time, simply by typing in your own format into the Date Format box within the Profile.
7 people found this helpful

Attachments

    Outcomes