web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Issue with date Format...
Microsoft Dynamics AX (Archived)

Issue with date Formats in Query Where clause

(0) ShareShare
ReportReport
Posted on by Faran Baig 125

Hi experts,I am facing a problem in x++ select statement with date in where clause. Issue is that the date coming from form control is in format 1/8/2019 (dd/mm/yyyy),but for query to return data i want date in this format 01\08\2019 dd\mm\yyyy).

Query screen shot is attached for reference.I also used BackOrderDate.dateValue() but it only changes month and day sequence.

824832.Capture.PNG

Anyone please guide help me how to fix it in x++.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    Trying to compare a date with a string is a mistake.

    Simply compare the values as dates, which will avoid all problems with string formats: retailTransactionTable.TransDate == BackorderDate.dateValue().

  • Faran Baig Profile Picture
    Faran Baig 125 on at

    Hi Martin

    I used retailTransactionTable.TransDate == BackorderDate.dateValue() ,but query does not return value ,

    at info  BackorderDate.dateValue() gave 1/8/2019. When given hard coded 01\08\2019 ,query worked right

    retailTransactionTable.TransDate == 01\08\2019

    Don't know how to compare them in right way.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    Using dateValue() is the right solution; there is no doubt about it.

    I know neither your data nor the runtime value in BackorderDate control, so can only guess why your query doesn't return any data when executed against your database.

    Try this:

    if (BackorderDate.dateValue() == 01\08\2019)
{
    info("It's exactly the same thing!");
}
else
{
    info("BackorderDate contains a different date!");
}
  • Faran Baig Profile Picture
    Faran Baig 125 on at

    Hi Martin,

    Still i am getting message BackorderDate contains a different date.

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    You said that you get the data for 01\08\2019 but not for the value from BackorderDate. And the test has proven that the reason is that BackorderDate doesn't contain the same date (01\08\2019). You're getting different results because you're running queries for two different dates.

    If you've set 1/8/2019 to BackorderDate but the result isn't equal to 01\08\2019, it might mean that your local format is m\d\yyyy, therefore it's 1st August and not 8th January.

  • Faran Baig Profile Picture
    Faran Baig 125 on at

    Hi Martin

    I get solved my problem by changing date control property that is DateFormat to MDY and using  str2Date(BackorderDate.valueStr(),123)   in where clause.

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    So you take a date, convert it to a string and then convert it to a date again. If you did it correctly, you would get the same date, so the code wouldn't have any effect. You don't get the same thing because you have a bug there.

    Your "solution" proves my theory that you confuse day and month. You're taking MDY format and then interpret is DMY, which means makes little sense and it won't work for days higher than 12. Try it with 31st January, for instance...

    You believe that your date control has a different date than it actually does (as we proved before), because you misinterpret the format.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans