Microsoft Dynamics AX

Forums

Home » Microsoft Dynamics AX » Microsoft Dynamics AX Forum
Related Search Results

AxaptaRecord.ExecuteStmt WHERE DateTime

by
on 25 Jun 2012
  • Suggested Answer

How to extract Date value from a DateTime field

by
on 18 May 2010
  • 1 comments

When 'createdDateTime' is applied

by
on 12 Apr 2011
  • Verified Answer
Troubleshooting an Issue?

Our support engineers have assembled the top recommended solutions for you.

Microsoft Dynamics AX 2012

CRM Connector in Microsoft Dynamics AX 2012

Financials Management in Microsoft Dynamics AX 2012

Upgrading to Microsoft Dynamics AX 2012


Microsoft Dynamics AX 2009

Application Object Server (AOS)

Enterprise Portal and Role Centers

Inventory Costing in Microsoft Dynamics AX 2009 

 Invoice Settlements/Discounts/Reversals

 SSRS and SSAS Integration

 Workflow

Bug in default value for CreatedDateTime fields

This question has suggested answer(s)
Jonas Bergman
Posted by
on 31 May 2012 4:34 AM

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
All Replies
  • Mahmoud Anass
    Posted by
    on 31 May 2012 3:55 PM

    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
  • Jonas Bergman
    Posted by
    on 1 Jun 2012 1:25 AM

    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.
  • mastervbnet
    Posted by
    on 3 Jun 2012 4:28 AM

    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 of “dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())” may solve the issue

     

    This might need to be fixed by Microsoft

     

    createddatetime utcDateTime modifiedDateTime GetUTCDate AX-2009
  • Jonas Bergman
    Posted by
    on 4 Jun 2012 4:42 AM

    "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
  • mastervbnet
    Posted by
    on 4 Jun 2012 8:21 AM
    Suggested Answer

    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

    createdatetime modifiedDateTime AX-2009