Microsoft Dynamics AX (Archived)

Any2Date and any2int64 conversions behave differently when executed on run to screen and when executed in batch

(0) Share

Report

Posted on by venkatesh vadlamani

3,180

Hi ,

I was debugging an issue related to a custom report developed a long time back by some other person.

The report is designed using StatementClass and ResultSet class to make use of SQL querying abilities since there is a large chunk of data that needs to be considered. I completely against usage of SQL and using Statement and s

2313.Issue.png

The conversion highlighted in green (any2date(resultSet.getString(32));) returns a valid date when the report is executed to screen. The class extends SrsReportDataProviderPreProcessedTempDB , so when run to screen it uses the current interactive session to execute the date provider.

So far so good.

But when the same code is executed in batch this conversion returns default date 1/1/1900

My question is whether there is an ambiguity in using any2date function when run in batch and when run on client. Please provide you thoughts on this.

"Please note that resultSet.getDate(int columnIndex) will resolve the issue. I am not asking for resolution using different conversions functions either , My question is any2Date function does it behave differently when run on client and when run on batch, or is it just one such weird occurrence"

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Dauvis 170 on at

Like (0)

Report

When the code is running in batch, it is using the CLR which in some instances does not necessarily give the same results that you would see running in the client. When running on the client, AX uses uses a different intermediate language and set of libraries. It looks like you found one of those instances.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

This is purely a guess, but my suspicion is that different date formats are in play on the client and server, and that the conversion from date to string (which occurs implicitly with .getString), and then from string back to date again (using any2date), fails under the server's date format. I think it's just as likely that the implicit conversion from date to string by .getString is responsible for the problem as any2date. Have you inspected the results of .getString (on the server) directly in your case?

Obviously the resultsSet.getDate() works because the original column is probably a date data type naturally, so no conversion occurs.

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi

As Dauvis said, the Batch process running in CIL, so .NET Language.

As per my esperience, it's always suggest to use the .NET methods in order to avoid different behaviour between CIL or not.

So, in your case, try with :

utcDateTime c;

Date d;

System.Globalization.CultureInfo culture = System.Globalization.CultureInfo::get_InvariantCulture();

c = System.DateTime::ParseExact(

ResultSet.....,

'M/d/yyyy',

culture);

d = DateTimeUtil::date(c);

Was this reply helpful? Yes No
Suggested answer

Iulian Cordobin 8,201 on at

Like (0)

Report

The any2date function is actually very strict on the format of the string it takes as an input, as well as it's value. If the string is not proper formatted in X++ it will throw an error. However, the same function in CIL will return an empty date, which will end up in X++ as datenull. So basically it has to do with what the function does when the input is not understood.

Was this reply helpful? Yes No
Suggested answer

Iulian Cordobin 8,201 on at

Like (0)

Report

Looked further more. The CIL code actually uses a str2date conversion as well, in case a string is passed, and the format of the date should be 321 (year-month-day). I believe you are getting a 1/1/1900 for dates that are not being passed in this format.

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

You didn't do enough to isolate the problem. Your code for getting data from database doesn't seem to be related to your problem. It seems that you have a problem with default conversion from string to date, therefore obtain the actual string value and focus on the conversion. Forget all the remaining code.

Was this reply helpful? Yes No
venkatesh vadlamani 3,180 on at

Like (0)

Report

Hi ,

Thank you for all your replies.

I have narrowed the issue to any2date function earlier.That is sole purpose I wanted some discussion on this as I couldn't find documentation on different behavior of this function client and server tier

The reason I strongly feel that any2date and any2Int64 functions behave differently on server and client tier is that I reproduced this issue on a single box developer environment where all service accounts are based on the same credentials (my credentials). Ax user local is EN-US , same is with windows user . Ideally date format is ruled out when server and client locale are considered as every thing is on EN-US locale

Now when I execute this saving the same to string variable and then convert it to date later I observe the following

1) String variable is assigned with full date time from sql when resultset.GetString(columnID) is used like "2016-01-14 00:00:00" irrespective whether its client execution or server execution.

2) Converting this to date on client tier gives me 1/14/2016 but on the server side the conversion never happens and the date variable default value is saved 1/1/1900

3) Converting an string to Int64 (recID ) also return correct value on client and 0 on server

Since there is no difference in locale I assume that this function behaves differently when run on server and when run on client

Regards

Venkatesh

Was this reply helpful? Yes No