Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Bug in default value for CreatedDateTime fields

Posted on by Microsoft Employee

Hi

Today we ran into a very strange issue in Ax 2009 when looking up records like this:

select Table1
where Table1.createdDateTime == Table2.createdDateTime

A record that had the exact same CreatedDateTime was never returned. After analyzing the table I noticed that out of 400.000 records, 145 records looked like this in the CreatedDateTime field:
2012-04-12 11:48:49.003

Since there were 3 milliseconds added to the value the query did not return the records. It took a while to understand this, since milliseconds are not presented in the table browser. They can only be seen in the query window of the SQL Server Management Studio.

After analyzing the table in the database I did find the reason, but not a solution for this.

The default value for CreatedDateTime field looks like this in the database:
dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())
This is how AX creates the fields.

What this default value is supposed to do is to return current date but strip the milliseconds.

The problem with this is that getutcdate() gets called twice. That means that there is a slight possibility for the method to return two different values, especially if there is a high load on the database. Also, SQL Server has a precision of 3 milliseconds, so milliseconds will be rounded to 3.

So, what happened in the 145 records of my table is this:

2012-05-30 11:20:30.567 - .564 = 2012-05-30 11:20:30.003

That is the reason. Now - does anyone have a solution?

/Jonas

*This post is locked for comments

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at
    RE: Bug in default value for CreatedDateTime fields

    Hi all,

    does anyone know if this is fixed in the meanwhile?

    regards

    Douglas

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Bug in default value for CreatedDateTime fields

    Since these two fields “createddatetime, modifieddatetime with associated default value/formula” are created at low-level execution between AX and SQL-Server during AOT-Tables Sync/Compilation Process

    This issue cannot be resolved by customizatin in AX

     

    Until it fixed by Microsoft, I suggest temporary workaround for this issue is to use direct SQL update before executing reports/queries in AX

     

    update tablename

    set createddatetime = convert(datetime, convert(varchar, createddatetime, 120)) where DATEPART(ms, createddatetime)>0

     

    update tablename

    set modifieddatetime = convert(datetime, convert(varchar, modifieddatetime, 120)) where DATEPART(ms, modifieddatetime)>0

     

    I recommend taking full database backup before executing sql-update and using separate sql-update statements to update createddatetime and createddatetime fields for quick/performance on large tables

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Bug in default value for CreatedDateTime fields

    "Nice" to see that I am not the only one. Currently I have no more support tickets with Microsoft, so I haven't had the possibility to report this - since you never know if they will charge you or not...

    I have sent this information to a person within the Microsoft support team by mail, but I have not heard anything from him yet. So if you have the possibility to report this, please do.

    /Jonas

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Bug in default value for CreatedDateTime fields
    Hi Jonas,
     
    We too found hundreds of records with exactly .003 milliseconds in some tables and thousands in few tables
    This also causes missing those records in filtering with createdatetime/modifieddatetime fields with table browser & grids in AX
     
    Using “convert(datetime, convert(varchar, getutcdate(), 120))” instead ofdateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())” may solve the issue
     
    This might need to be fixed by Microsoft

     

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Bug in default value for CreatedDateTime fields

    That might be related to the timezone functionality of Ax. Dates are stored as UTC and timezone is applied when displaying on the screen.

    Check msdn.microsoft.com/.../cc589382(v=ax.50).aspx

    I have no experience when it comes to SSRS, though.

  • m_anass Profile Picture
    m_anass on at
    Re: Bug in default value for CreatedDateTime fields

    I've somehow similar issue, I've a record in AX with utcDateTime value 2012-03-30 12:20:30 shown on the form, but when I'm running SSRS report with 2012-03-29 the record with utcDateTime value "2012-03-30 12:20:30" is shown in the result !!!

    Any solutions/recommendation?

    Mahmoud

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans