You’re offline. This is a read only version of the page.
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
Hi all,
does anyone know if this is fixed in the meanwhile?
regards
Douglas
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
"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
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,228
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (