Dealing with Amazon MP Date Formats

Miklos_HollenderMiklos_Hollender Member Posts: 1,597
edited 2020-02-03 in NAV Three Tier
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? :)

Answers

  • Miklos_HollenderMiklos_Hollender Member Posts: 1,597
    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'...
  • rubberduckyrubberducky Member Posts: 5
    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);
    
Sign In or Register to comment.