Thursday, May 8, 2008

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:


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

3 comments:

The Bald Guy said...

Thank you - I was working on an integration over a remote desktop that the client was watching, and I couldn't remember for the life of me how to reformat my date in vbscript. Your site came up during a hurried google search, and you saved the day... I may not have mentioned how instrumental your assistance was to the client though :). Awesome blog - looking forward to spending some more time on it as soon as I have some extra bandwidth!

Anonymous said...

Anyone had this issue: I have dates in my file like 1/9/12 and 1/15/12. The lines with the 1/9 integrate in IM just fine. It says the lines iwth 1/15 are not in balance. If I delete the lines with 1/9 and re-run the 1/15 lines go through and integrate fine. It has to to do with the date but I am not sure what. I have opened my csv in excel and formatted the dates as MM/DD/YYYY. Open it in notepad and I see them as MM/DD/YYYY. When I preview in IM it is 1/9/2012....

Mariano Gomez said...

@Anonymous: What integration destination are you referring to?