Supported Date Formats in Integration Manager
The topic of date formats supported by Integration Manager comes up very often in discussion boards and online community forums around the web, especially overseas where date formats tend to be distinct from the typical US format. Even here in the US, it is necessary to make certain adjustments to the date format if characters other than the forward slash ("/") are introduced as separators.
Currently, Integration Manager will accept date formats that are in the order of month-day-year. Using the date 05/01/08, the following formats will be acceptable within your source file or table:
When converting other date formats, it will be necessary to introduce VBScript into the mapping of the date field to standardize to any of the aforementioned formats.
Let’s assume a transaction date of '20080501' (YYYYMMDD) is given in a file, to be converted to the traditional US format of MM/DD/YYYY. The following VBScript would be added to the transaction date field, as follow:
Now let's assume your date format is DD/MM/YYYY. The following script will properly format the date for Integration Manager to MM/DD/YYYY:
This post has dealt with the topic of date formatting. My next topic will talk about dropping timestamps from source date fields using SQL and VBScript, depending on the source.
As always, drop me a note letting me know what you think about this post or commenting on your personal experiences with the subject.
Until then,
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Currently, Integration Manager will accept date formats that are in the order of month-day-year. Using the date 05/01/08, the following formats will be acceptable within your source file or table:
MM/DD/YY 05/01/08
MM/DD/YYYY 05/01/2008
M/D/YY 5/1/08
M/D/YYYY 5/1/2008
MMDDYY 050108
MMDDYYYY 05012008
When converting other date formats, it will be necessary to introduce VBScript into the mapping of the date field to standardize to any of the aforementioned formats.
Let’s assume a transaction date of '20080501' (YYYYMMDD) is given in a file, to be converted to the traditional US format of MM/DD/YYYY. The following VBScript would be added to the transaction date field, as follow:
' Created by Mariano Gomez, MVP
' No warranties conferred, express or implied
' Assign the source date to a local script variable
sDate = SourceFields("Query.Transaction_Date")
fDate = Mid(sDate, 5, 4) & Mid(sDate, 1, 4) ' Move the year at the end of the date string
CurrentField.Value = fDate ' Map the transposed date field to the transaction field
Now let's assume your date format is DD/MM/YYYY. The following script will properly format the date for Integration Manager to MM/DD/YYYY:
' Created by Mariano Gomez, MVP
' No warranties conferred, express or implied
' Assign the source date to a local script variable
sDate = SourceFields("Query.Transaction_Date")
fDate = Mid(sDate, 4, 2) & Mid(sDate, 1, 2) & Mid(sDate, 7, 4)
CurrentField.Value = fDate ' Map the transposed date field to the transaction field
This post has dealt with the topic of date formatting. My next topic will talk about dropping timestamps from source date fields using SQL and VBScript, depending on the source.
As always, drop me a note letting me know what you think about this post or commenting on your personal experiences with the subject.
Until then,
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Comments