How do i convert this CSV custom format to ValidTo date format in X++

(0) Share

Report

Posted on by Mav

Hi expert,

I have csv file which will send date as below format only,i.e. cannot ask user to make any changes in this format .

I also have code to convert date to Ax acceptable date which works when the format of date is as date in CSV but not for below scenario.

Please suggest how do i fix this in way that the below CSV format is converted into Ax date format currently it keeps throwing the error from dateNull() code block

CSV format

AX code for converting to Ax acceptable date format. (Validto Date.)

Please note mytable.myDate EDT is Validto Date.

My Main class
{
               myTable.myDate   = this.ConvertDates(conPeek(_record,9));

                if (myTable.myDate == dateNull())
                {
                    throw error(strFmt('%1 Error Invalid Date',DateTimeUtil::getSystemDateTime()));
                }
}   

protected date convertDates(str dateTime)
{
    try
    {
        if (strLen(datetime) > 10)
        {
            return DateTimeUtil::date(str2DateTime(datetime,321));
        }
        else
        {
            return str2date(datetime,213);
        }
    }

    catch (Exception::Error)
    {
        throw error (strfmt("@SYS63093",funcName()));
    }
}

I have the same question (0)

All responses (10)

Answers (4)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Mav,

Is it not working for the scenario where the date is "10/30/2020 12:00:00:AM"?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,014 Super User 2025 Season 2 on at

Like (0)

Report

Hi Mav,

You have opened the CSV file in Excel. Can you open the CSV file in e.g. Notepad and share the format as shown there?

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

What value do you have in dateTime variable in convertDates() method? And what does it return? Debugging your code should have been your first thing to do.

It would also help if you told us more about your problem than just that it doesn't work.

Your code will fail if dateTime isn't in the precise format expected by str2DateTime(). I suspect that's the case. You may need a better way of parsing, such as using System.DateTime::ParseExact().

Was this reply helpful? Yes No
Mav on at

Like (0)

Report
HI all ,

Thanks for your responses.

@Gunjan I cannot try that format as the user receives this format from a system & they cannot change anything at their end,

@Andre while opening it in notepad it shows like "2020-10-30 00:00:00.000"

@Martin tried below code, unable to debug as job wont run & there are no errors.

Please advise

static void dateCOnvert (Args _args) { str myDateStrFormat = '2020-10-30 00:00:00.000'; ValidToDate myTargetDateFormat; System.Globalization.CultureInfo culture = System.Globalization.CultureInfo::get_InvariantCulture(); myTargetDateFormat = System.DateTime::ParseExact(myDateStrFormat,'MM/dd/yyyy H:mm',culture); info(strFmt("%1",myTargetDateFormat)); pause; }

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report
Hi Mav,

Since you don't need the time, can you not try dealing with the date part only by splitting the dateTime value?

str dateStr = "2020-10-30 00:00:00.000"; info(strFmt("%1", str2Date(subStr(dateStr, 0, 10), 321)));

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

If your dateConvert() doesn't run, debug the code that should be calling it and fix the bug.

Nevertheless the method is wrong too. '2020-10-30 00:00:00.000' obviously doens't match the format 'MM/dd/yyyy H:mm'. You would need something like 'yyyy-MM-dd HH:mm:ss.fff'. Your current implementation would throw an exception, which you forgot to catch, therefore you won't see it in the infolog. Tha's another bug you must fix.

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

Hi Martin

Could you please share where do i get this information like year is small case yyyy Month is upper case M, ff is i presume fraction and lower case.

Also when using try catch it just gives

Thanks

Mav

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report

You can find these format definitions in documentation: Custom date and time format strings.

Unfortunately I don't understand what you mean by "Also when using try catch it just gives".

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

My bad for that incomplete sentence which i was about to delete.

Could you suggest how do i make this dynamic for instance instead of specifying 'yyyy-MM-dd HH:mm:ss.fff' in System.DateTime::ParseExact(myDateStrFormat,'yyyy-MM-dd HH:mm:ss',culture); line to something which could detect the format being sent & then change 'yyyy-MM-dd HH:mm:ss.fff' accordingly to M/dd/yyyy H:mm if the format being sent is that.

I could try adding condition using switch case or if else but there can be so many combination hence wondering if there is way we could detect what is being sent & then change it dynamically.i.e. detect what format is being sent & then put that format in System.DateTime::ParseExact(myDateStrFormat,'format sent',culture) line

Also i have looked at this overload of formats for parse exact where we would need to specify the expected formats vs it could detect the format being sent & then fill it in format parameter itself.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report

If a universal solution was possible, we wouldn't need ParseExact() at all. Unfortunately it can't be done. Here is a proof:

Let's say you have this string: 01/02/2020. Does it mean 1st February or 2nd January? It's not possible to say without context.

The right solution may be ensuring that the CSV files are generated with consistent formats.

Was this reply helpful? Yes No