Dealing with Amazon MP Date Formats

Miklos_Hollender

An Amazon Marketplace VAT Calculation report no matter in what language you download it, lists dates as "30-Jan-2020 UTC" which cannot possibly be EVALUATED into a NAV 2015 Date or Datetime in a German settings computer. I suppose I have to do string processing with the month names in a CASE statement and all that. Just asking if someone already wrote that function so I can save that effort. I am not the first one to sell stuff on Amazon and needing to import their data into NAV, right?

Miklos_Hollender

My first take:

Date Test - OnAction()

MESSAGE(FORMAT(AMDate('23-Oct-2019 UTC')));
MESSAGE(FORMAT(AMDate('01-Mar-2020 UTC')));
MESSAGE(FORMAT(AMDate('04-May-2020 UTC')));

LOCAL AMDate(TestText : Text) : Date

//get rid of " UTC"
ShortenedText := COPYSTR(TestText,1,STRLEN(TestText)-4) ;

//parse into text
DayTxt := COPYSTR(ShortenedText,1,2);
MonthLongTxt := COPYSTR(ShortenedText,4,3);
YearTxt := COPYSTR(ShortenedText,8,4);
//MESSAGE('Day:'+DayTxt+' Month:'+MonthLongTxt+' Year:'+YearTxt);

//parse into int
EVALUATE(DayInt,DayTxt);
CASE MonthLongTxt OF
  'Jan': MonthInt :=1;
  'Feb': MonthInt :=2;
  'Mar': MonthInt :=3;
  'Apr': MonthInt :=4;
  'May': MonthInt :=5;
  'Jun': MonthInt :=6;
  'Jul': MonthInt :=7;
  'Aug': MonthInt :=8;
  'Sep': MonthInt :=9;
  'Oct': MonthInt :=10;
  'Nov': MonthInt :=11;
  'Dec': MonthInt :=12;
END;
EVALUATE(YearInt,YearTxt);
//MESSAGE('Day:'+DayTxt+' Month:'+format(monthint)+' Year:'+YearTxt);


EXIT( DMY2DATE(DayInt,MonthInt,YearInt) );

Works as long as the day is '01' and not '1'...

rubberducky

Nice, I might need that later.

//get rid of " UTC"
ShortenedText := COPYSTR(TestText,1,STRLEN(TestText)-4) ;

is basically deadcode, result will be the same if you get rid of that line.

Works as long as the day is '01' and not '1'...

you could change the way you split the string to make it more robust.

//split at '-' and space
CommaSeparatedTxt := CONVERTSTR(TestText,'- ',',,');
DayTxt := SELECTSTR(1,CommaSeparatedTxt);
MonthLongTxt := SELECTSTR(2,CommaSeparatedTxt);
YearTxt := SELECTSTR(3,CommaSeparatedTxt);