Bug in default value for CreatedDateTime fields

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (6)

Answers (0)

m_anass on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

"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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

Hi all,

does anyone know if this is fixed in the meanwhile?

regards

Douglas

Was this reply helpful? Yes No