Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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:

enter image description here

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.

like image 409
AYETY Avatar asked Sep 30 '15 07:09

AYETY


1 Answers

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.

like image 61
Tom V Avatar answered Sep 21 '22 21:09

Tom V