Microsoft Dynamics GP (Archived)

Integration Manager Script change Date Format

(0) Share

Report

Posted on by Abdo Khoury

14,855

Dear

I am trying to change the date format while importing dates in Integration Manager

my dates in the source file are DD/MM/YYYY and i want to change them to MM/DD/YYYY

i tried the below script

CurrentField = SourceFields(month("FA1.Acquisition Date")) + SourceFields(day("FA1.Acquisition Date")) + SourceFields(year("FA1.Acquisition Date"))

but i am getting

DOC 1 ERROR: Error Executing Script 'Acquisition Date' Line 1: - Type mismatch: '[string: "FA1.Acquisition Date"]'

i also tried

CurrentField = SourceFields(month("FA1.Acquisition Date")) +"/"+ SourceFields(day("FA1.Acquisition Date")) +"/"+ SourceFields(year("FA1.Acquisition Date"))

i think GP is understanding them as string , any idea's how i can reach MM/DD/YYYY

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

soma 24,410 on at

Like (0)

Report

Better you just change the date values format in your source file and integrate into GP.

Hope this helps!!!

Was this reply helpful? Yes No
Don Wisch 660 on at

Like (0)

Report

Since it thinks it's a string then manipulate it like a string... pulling it apart and putting it back together in the format you like...

CurrentField = SourceFields(mid("FA1.Acquisition Date"),3,2) + "/" + SourceFields(left("FA1.Acquisition Date"),2) + "/" + SourceFields(right("FA1.Acquisition Date"),4)

Haven't confirmed this works but give it a try :)

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

What is the data type of the source record field?

Kind regards,

Leslie

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Have you considered treating the field like text and using Left and Mid statements to parse the data?

CurrentField = SourceFields(Mid("FA1.Acquisition Date",4,2)) + "/" + SourceFields(Left("FA1.Acquisition Date",2)) + "/" + SourceFields(Mid("FA1.Acquisition Date",7,2))

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (1)

Report

I missed Daniel's response, prior to posting my own. Daniel's looks a little off, as MID(field,3,2) would grab the first "/" character. Having reviewed the entire post, Soma's response has merit as well. If you are pulling this data from SQL, you would likely speed processing of this Integration by converting the field using SQL script on export, rather than parsing the field using Integration Manager. The script language below converts dates into US short date format.

convert(varchar(10),Table.Field,101)

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Silvano,

I am also encountered the similar issue.

Just followed the method mentioned in the below link. My source excel file contains acquisition dates as "dd/MM/yyyy" format.

Using the script(link below), modified the code to convert "dd/MM/yyy" to "MM/dd/yyyy". It just imported all the records.

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2012/05/28/working-with-dates-in-integration-manager-vbscript/

Please let me know, if similar method works for you.

Naushad. C. K.

Was this reply helpful? Yes No