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

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AX 2012 - issue with s...
Microsoft Dynamics AX (Archived)

AX 2012 - issue with str2DateTime function

(0) ShareShare
ReportReport
Posted on by Preeti Sonambekar 1,245

Hi All,

Below is the x++ query I am using. I have two primary addresses for the same vendor, one is active
and one is outdated. I have to update only the active rows. In table this is how the data looks.

isPrimary accountnum VALIDTO
0  DAT-000001 2154-12-31 23:59:59.000
1  DAT-000001 2011-08-26 21:54:57.000
1  DAT-000001 2154-12-31 23:59:59.000

static void Job2(Args _args)
{
    logisticslocationext a9;
    DirPartyLocation a6;
    LogisticsPostalAddress a8;
    vendtable a2;
    logisticslocation a7;
    ;
   
    update_recordset a9
    setting taxgroup = '' join
    a6 where a6.LOCATION == a9.LOCATION && a6.ISPOSTALADDRESS == 1
    join a8 where a8.LOCATION == a6.LOCATION
    && a8.ValidTo == str2DateTime("2154-12-31 23:59:59",321)
    join a2 where a2.PARTY == a6.PARTY
    join a7 where a7.RECID == a8.LOCATION && a7.RECID == a6.LOCATION && a7.RECID == a9.LOCATION;
}

When I run this job, it updates all the three records. I don't want to touch the one
with validto date as: 2011-08-26 21:54:57.000 as it is outdated

Not sure what is wrong with "a8.ValidTo == str2DateTime("2154-12-31 23:59:59",321)". Please suggest what might be going wrong.

Thanks,
Preeti

*This post is locked for comments

I have the same question (0)
  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    try this

    utcDateTime2SystemDateTime(a8.ValidTo) == utcDateTime2SystemDateTime(str2datetime("2154-12-31 23:59:59", 321));

  • Preeti Sonambekar Profile Picture
    Preeti Sonambekar 1,245 on at

    I get the following error with the above change: "Illegal use of Where expression"

    I think on the left side of expression, use of any function is not allowed. If I put  "utcDateTime2SystemDateTime" only on the right side, the it does not update any record. please suggest as it is very urgent.

  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    try this then

    a8.ValidTo == utcDateTime2SystemDateTime(str2datetime("2154-12-31 23:59:59", 321));

  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    actually the problem the format is different

    try doing this

    print str2datetime("2154-12-31 23:59:59", 321);

    pause;

  • Preeti Sonambekar Profile Picture
    Preeti Sonambekar 1,245 on at

    you are right, when i print it, it shows: 12/31/2154 11:59:59 pm. but how do i resolve it? in below statement it does not allow me to use any functions on the left hand side i.e. a8.ValidTo, which is a table field and has value 2154-12-31 23:59:59.000. Please suggest how do i compare this? my requirement is to assign taxgroup = empty string, only to the records having ValidTo date as      2154-12-31 23:59:59.000

    a8.ValidTo == utcDateTime2SystemDateTime(str2datetime("2154-12-31 23:59:59", 321));

    please suggest.

  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    I dont know why you are seeing the date in AX "2154-12-31 23:59:59.000"

    Things you can try is print a8.ValidTo to see if you are getting the values right.

    If the task is that urgent then why dont you direct sql and update the record.

  • Preeti Sonambekar Profile Picture
    Preeti Sonambekar 1,245 on at

    hey thanks for replying. we have a load utility which uses x++ queries thru business connector to communicate with Axapta. We got the recommendation from microsoft that we should use business connector instead of direct sql to avoid recid issues. that's the reason we are not using direct sql. and this issue we are having only in AX 2012 because now they are storing all the historical data (AX 2009 didn't have this concept).

    what do you mean by "I dont know why you are seeing the date in AX 2154-12-31 23:59:59.000"?

    when i go to vendor form and create a vendor with primary address, it gets stored in logisticspostaladdress table with validfrom date as the creation date and validto date as "2154-12-31 23:59:59.000". this is what i see when i query the table.

  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    I meant when i look into the tables on my local machine I see the date in the following format

    12/31/2154 11:59:59 pm

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi,

    You can't use built in function calls in x++ query syntax unless they are allowing this in AX 2012.  :)

  • 640579632b5c4e6780a624c4771b6f93 Profile Picture
    640579632b5c4e6780a... 4,730 on at

    @Kent you are right.functions are not allowed in query even 2012. Thanks for correction

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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 16

#2
Nagendra Varma K Profile Picture

Nagendra Varma K 4

#2
Harisgillani Profile Picture

Harisgillani 4

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans