web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Conversion failed when...
Microsoft Dynamics AX (Archived)

Conversion failed when converting date and/or time from character string

(0) ShareShare
ReportReport
Posted on by Johnny 6,478

Hello,

When I try to post timesheet in one of the entities I'm getting following error: "SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string"

All other entities are ok. 

What do you suggest to check? Maybe date format is different for this entity? Where should I check it?

*This post is locked for comments

I have the same question (0)
  • Pir_Rana Profile Picture
    Pir_Rana 836 on at

    Hi,

    Is that field has type Datetime or UTC datetime?

  • Johnny Profile Picture
    Johnny 6,478 on at

    Hi,

    The problem comes from AccountingEventSourceDocumentProcessor.createAccountingEvents() methods select statement.

    The problem is that it can't find accountingDistribution.AccountingDate field is empty and is passed to AccountingEventSourceDocumentProcessor::createAccountingEvent method.

    This is the case only for one entity.

    Can you advice any way what to check / change?

  • Johnny Profile Picture
    Johnny 6,478 on at 
    public static server void createAccountingEvents(SourceDocumentHeaderRecId _sourceDocumentHeaderId, AccountingEventType _accountingEventType, AccountingEventState _accountingEventState)
{
    SourceDocumentLine              sourceDocumentLine;
    AccountingDistribution          accountingDistribution;
    AccountingEvent                 existingAccountingEvent;
    
    ttsbegin;
    
    // Need to create each event one by one since more than one accounting event with the same event type could exist on a same day.
    while select AccountingDate from accountingDistribution
        group by accountingDistribution.AccountingDate
        where accountingDistribution.AccountingEvent == 0 &&
             accountingDistribution.SourceDocumentHeader == _sourceDocumentHeaderId
        exists join sourceDocumentLine
            where sourceDocumentLine.RecId == accountingDistribution.SourceDocumentLine &&
                (sourceDocumentLine.AccountingStatus == SourceDocumentLineAccountingStatus::Completed ||
                sourceDocumentLine.AccountingStatus == SourceDocumentLineAccountingStatus::Canceled)
        notexists join existingAccountingEvent
            where existingAccountingEvent.AccountingDate == accountingDistribution.AccountingDate &&
                existingAccountingEvent.State == AccountingEventState::Started &&
                existingAccountingEvent.SourceDocumentHeader == _sourceDocumentHeaderId &&
                existingAccountingEvent.Type == _accountingEventType
    {
        AccountingEventSourceDocumentProcessor::createAccountingEvent(_sourceDocumentHeaderId, accountingDistribution.AccountingDate, _accountingEventType, _accountingEventState);
    }

    ttscommit;
}

    The "_sourceDocumentHeaderId" value can't be found to AccountDistribution table.

    I don't know where to check what

  • 5400 Profile Picture
    5400 7,162 on at

    If there no customization, can you sync the table and do the Full CIL.

  • Johnny Profile Picture
    Johnny 6,478 on at

    Hi,

    If this is the case of sync and CIL, shouldn't this problem be for all entities in AX? The problem is only for 1 entity. Maybe there is some per company setup to apply, but I don't know which

  • Habib Syed Profile Picture
    Habib Syed 45 on at

    I faced a similar issue. I tried deleting the table's field from SSMS and then synchronized the table from AOT.

    The problem occurred after I restored the AX model store from another system. Try that. I hope it works.

  • Johnny Profile Picture
    Johnny 6,478 on at

    Thanks, I will try.

    Did you also have problem only for 1 entity and other entities worked ok?

  • Johnny Profile Picture
    Johnny 6,478 on at

    Now other entities also have same error message

  • Johnny Profile Picture
    Johnny 6,478 on at

    Hi Pir Mohammad,

    the field is AccountingDate in AccountingDistribution table

  • Suggested answer
    dolee Profile Picture
    dolee 11,279 on at

    For the table that is causing trouble, check the fieldId in AOT and compare them to what is stored in SQLDictionary. If you see any differences, update the SQLDictionary table to match what's in AOT.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans