Trouble with date object in while select statement

(0) Share

Report

Posted on by Community Member

Hi ,

First time posting here. Hope someone can help out.

I have a while select query which needs to find records based on the REQUEST_DELIVERY_TIME field of a View. The field is stored as a date in the AX DB (I have no control over this) and the method which does calls the select statement takes in a string as a date (again, I have zero control over this as I am creating an X++ Service to talk to an existing .Net application. I converted the string to a utcdatetime object (this same piece of logic is used in simlar methods quering very similar views where the equivalent REQUEST_DELIVERY_TIME field is actually a utcdatetime- again, I'm afriad i'm stuck with a date for this particular view).

There is a problem with the where clause of my while select as its not identifying the dates as equal.

For the purpose of this example, there is only 1 record in the view I'm querying...

I wrote a little piece of code and retrieved the date im loking for from DB (with the select first only statement below) and then debugged and compared it to the date object I got from converting the string passed into the method to utcdatetime and then to date.... and they are EQUAL!.... yet AX doesnt say they are equal at all in the ensuing while select ... I'm really stuck on this. Am I missing something to do with Time zones?

Thanks in advance for any help.

provider = System.Globalization.CultureInfo::get_CurrentCulture();

datePattern = 'yyyyMMddhhmmss';

dt = System.DateTime::ParseExact(time, datePattern, provider);

select firstonly * from openRequestVw;

temp = openRequestVw.REQUEST_DELIVERY_TIME;

temp1 = DateTimeUtil::date(dt);

if (temp == temp1)

{

print("hello"); // this code gets hit as the dates are equal

}

while select * from openRequestVw

where openRequestVw.HUB_NAME == hubName && openRequestVw.REQUEST_DELIVERY_TIME == DateTimeUtil::date(dt);

{

// do stuff here

}

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

I'm surprised that your code compiles at all. The semicolon in your WHERE condition looks like a bug.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin,

Apologies, that was a typo from copying and pasting code over to original message.

I'll provide code again with no typos and clear notes

str time = "20171001120005"; //example of time string being passed to method

provider = System.Globalization.CultureInfo::get_CurrentCulture();

datePattern = 'yyyyMMddhhmmss';

dt = System.DateTime::ParseExact(time, datePattern, provider);

select firstonly * from openRequestVw;

temp = openRequestVw.REQUEST_DELIVERY_TIME;

temp1 = DateTimeUtil::date(dt);

if (temp == temp1)

{

print("hello"); // this code gets hit as the dates are equal

}

while select * from openRequestVw

where openRequestVw.HUB_NAME == hubName && openRequestVw.REQUEST_DELIVERY_TIME == DateTimeUtil::date(dt)

{

// do stuff here

// this is not getting hit, the where clause doesn't evaluate the dates as equal for some reason

}

Thanks again,

Johnny

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Let me paste the code using </> button in the rich formatting view, which makes it a bit more readable:

str time = "20171001120005"; //example of time string being passed to method provider = System.Globalization.CultureInfo::get_CurrentCulture(); datePattern = 'yyyyMMddhhmmss'; dt = System.DateTime::ParseExact(time, datePattern, provider); select firstonly * from openRequestVw; temp = openRequestVw.REQUEST_DELIVERY_TIME; temp1 = DateTimeUtil::date(dt); if (temp == temp1) { print("hello"); // this code gets hit as the dates are equal } while select * from openRequestVw where openRequestVw.HUB_NAME == hubName && openRequestVw.REQUEST_DELIVERY_TIME == DateTimeUtil::date(dt) { // do stuff here // this is not getting hit, the where clause doesn't evaluate the dates as equal for some reason }

Is the part about parsing the string relevant to the problem? For example, what if you initialize dt by DateTimeUtil::newDateTime()? It will either simplify your test cases, or reveal that something is fishy there.

Also, you can use review the actual SQL code; I assume that it won't be difficult to spot the problem there. Then we can look at the root cause.

Here is how to get SQL:

select generateOnly forceLiterals openRequestVw where openRequestVw.HUB_NAME == hubName && openRequestVw.REQUEST_DELIVERY_TIME == DateTimeUtil::date(dt);
info(openRequestVw.getSqlStatement());

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report
Your first statement selects record in DB without any where clause, second statement has one

openRequestVw.HUB_NAME == hubName

You told us that there is only one record in DB, so does HUB_NAME equal to hubName ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for your help guys. I have resolved the issue using you advice above Martin. I used the a computed column to cast the field as a utcdatetimeobject.

Was this reply helpful? Yes No