Dynamics AX 2012: Conversion failed when converting date and/or time from character string

Question

In AX I have several entities. When I try to post unposted timesheets it works fine for all entities except of one where I'm getting SQL error: "Conversion failed when converting date and/or time from character string"

The call stack is below:

In highlighted method I see that it cannot find any SourceDocumentHeader in AccountDistribution table, so the AccountingDate is empty.

Has anybody experienced same problem and knows how to solve it? It is strange for me because all other entities works OK.

Thanks.

Answer 1

The technical explanation of what you are seeing is that this part of the code generates invalid SQL, but it looks to me as if you have a problem with your setup.

If you run date2str on an empty date it returns an empty string. Please try this in a job and you will see an empty string in the infolog.

static void TestEmptyDate(Args _args)
{
    AccountingDate _date;
    ;
    info(date2str(_date, 321, 2, 3, 2, 3, 4, DateFlags::None));
}

That then gets concatenated in the method updateDistributionsForEvent to generate an SQL statement:

sqlStatementText = strFmt('UPDATE T1 SET ACCOUNTINGEVENT=%1,RECVERSION=%2 FROM ACCOUNTINGDISTRIBUTION T1 WITH (INDEX(I_7452SOURCEDOCUMENTHEADERIDX)) CROSS JOIN SOURCEDOCUMENTLINE T2 ', _accountingEventRecId, xGlobal::randomPositiveInt32());

sqlStatementText += strFmt('WHERE (((T1.PARTITION=%1) AND (T1.ACCOUNTINGEVENT=0) AND (T1.ACCOUNTINGDATE={ d\'%2\'})) AND (T1.SOURCEDOCUMENTHEADER=%3)) AND ', getcurrentpartitionrecid(), sqlDate, _sourceDocumentRecId);
sqlStatementText += strFmt('((T2.RECID=T1.SOURCEDOCUMENTLINE) AND (T2.ACCOUNTINGSTATUS=%1 OR T2.ACCOUNTINGSTATUS=%2)) AND (T2.PARTITION=%3)', enum2int(SourceDocumentLineAccountingStatus::Completed), enum2int(SourceDocumentLineAccountingStatus::Canceled), getcurrentpartitionrecid());

Where T1.ACCOUNTINGDATE={ d\'%2\'} is the relevant part which generates T1.ACCOUNTINGDATE={ d''} in the SQL string.

If you try running

select  {d''}

in SQL you will get

Msg 241, Level 16, State 3, Line 1 Conversion failed when converting date and/or time from character string.

because an empty string cannot be parsed to a date.