Code highlighting

Tuesday, February 16, 2010

UtcDateTime in Dynamics AX 2009

In Dynamics AX 2009, Microsoft introduced a new data type, UtcDateTime, that is going to eventually replace the 2 existing types, Date and Time, which are still present in the application right now.
Obviously, the introduction of this new type requires a tutorial on how it can be used on forms, how you can filter on fields of this type, as well as what functions are available out of the box for it.
So I have made such a tutorial, and I hope it will be useful for developers upgrading to AX 2009.

Download the xpo for the tutorial from my SkyDrive

The tutorial consists of a single form, containing the following elements:
  • a grid, displaying data from CustTable
  • 4 buttons for various filtering actions
  • 3 controls that allow specifying the filtering conditions for the data
. In the form, you can see how UtcDateTime based controls are displayed both in a regular group and in a grid.

Dynamics AX UtcDateTime tutorial form

Below is an explanation of the implemented functionality, in form of a Question/Answer section:
  1. Q: Can I filter on the new UtcDateTime type, specifying the Date part only?
    A: Yes. You simply have to specify only the date part when applying the filter, like below. Note, that this also works fine when filtering directly from the UI (Ctrl+F).
    qbdsCustTable.addRange(fieldNum(CustTable, CreatedDateTime)).value(queryValue(DateFilter.dateValue()));
    What is interesting is how the kernel processes this range. In the below infolog, you can see that when viewing the query, it displays a "==" condition on a specific dateTime value.
    But in reality, as you can see from the SQL trace, a range ">= && <=" condition is applied to span exactly one day.
    Also note, that the values in the trace are displayed accounting for the TimeZone I am in, as well as for Daylight Saving Time

    SQL trace for Date filter on UtcDateTime field
  2. Q: Can I filter on the new UtcDateTime type, specifying the Time part only?
    A: No, this is not possible with a UtcDateTime type. The range applied when specifying a Time value is the minimum DateTime value, as seen below. Note, that in the SQL trace it is converted to "no range".

    SQL trace for Time filter on UtcDateTime field
  3. Q: Can I use similar query functions for UtcDateTime type?
    A: Yes. All the main existing functions for working with QueryBuildRange also support UtcDateTime. For example, in the infolog below you can see how a range on 2 UtcDateTime dates is applied. Global::queryRange method was used to achieve that. Note, again, that the SQL trace offsets the DateTime by the appropriate number of hours based on my location.

    SQL Trace for UtcDateTime range
  4. Q: How is the UtcDateTime stored in the database? Is it displayed the same way on forms?
    A: The UtcDateTime fields are in the database always stored in Coordinated Universal time (UTC). Whenever displayed on forms and bound to table fields, the data is converted to the user's preferred timezone. Note, that you need to take care of the conversion yourself, if the control is not bound to a field. For an example, see the init method of the tutorial form.
  5. Q: What standard helper functions are present for working with UtcDateTime type in the application?
    A: The main entry point for working with UtcDateTime type is the DateTimeUtil class. It allows adding Days/Months/Years, as well as applying an offset, getting the user's preffered timezone, converting from/to other types, etc. An example from the form init method is posted below:
        // getSystemDateTime() returns the current DateTime set in the system, not the current machine dateTime.
        // Note that getSystemDateTime() returns a UTC date and time, not your local date time.
        // In order to receive your local DateTime value, you should use methods applyTimeZoneOffset and specify the preferred time zone.
        utcDateTimeFilter.dateTimeValue(
            DateTimeUtil::applyTimeZoneOffset(
                DateTimeUtil::getSystemDateTime(),
                DateTimeUtil::getUserPreferredTimeZone()));
  6. Q: Does this mean that the support for Date and Time types has been removed?
    A: No, Date and Time are still supported. As you can see in the form init method, SystemDateGet(), timeNow(), today() are all still supported
  7. Q: I don't see the actual filter values in the SQL log. Instead, all I see are "?"'s. Also, how can I limit the number of data/fields selected from the database?
    A: This is just some extra stuff, not related to UtcDateTime, but still useful to know and pay attention to.
    CustTable has a very large number of fields, and I am only displaying 4 of those in the form, so it would be unwise to always query and return all of the fields. Luckily, the datasource has a property OnlyFetchActive, which controls the query behavior by only selecting the fields actually displayed on the form. Note, that you should avoid using this with editable datasources. See comments to this post for details
    As for "?"'s in the SQL trace - that is happening due to the use of placeholders. This in general optimizes the performance of the queries, by creating a query execution plan and storing it for future use. But it is possible, and is required in some specific cases, to force the use of literals (meaning the actual values of the ranges in the query). This can be done using the literals method on the query. See method init on the form for an example.

70 comments:

  1. I have just a question out of context:
    How do you do to have this text editor?
    You copy the X++ Code in a special editor?

    ReplyDelete
  2. E-mail me, and I will forward you the details of the changes you need to make to your blogger template in order to allow for this.

    ReplyDelete
  3. Hi,
    Q:what's about a "group by" on the date-part?
    is there an easy way to do this?

    SebDra

    p.s.:a comment to A7: using OnlyFetchActive can be dangerous when the datasource is not readonly.
    try it yourself: make a form with a custtable-datasource, set onlyfetchactive and put the fields accountnum,name,partyid,language,custgroup and currency onto it.
    edit the content of the name-field and watch on the custtable-standard-form what happens with the namealias-field.

    ReplyDelete
  4. 2 Anonymous:
    About OnlyFetchActive: Totally agree. And, as you can see, the datasource is ReadOnly in the example. But I think this is important enough to mention in the post, for beginners.

    About GroupBy on Date part: Not possible, sorry.

    ReplyDelete
  5. Nice Vanya, I'll make the translation to pt-br and link to your post! Congratulations!

    ReplyDelete
  6. hi vanya .. its me again.

    btw ... have try to export n import in AX2009.
    i have a problem with that.
    its weird ...

    i have two instance/AOS for example A n B.
    at 'A'&'B' i hve a same table(ex. TransportTable) with the same structure, ...its 5 Field. At 'A' i reduced the field so i have 3 Field for TransportTable.

    After that iam import the TransportTable at 'A' to 'B', but the result is ... TransportTable at 'B' is not Changed. TransportTable at 'B' its still have 5 field. wwhhyyyy??? helllllppp

    ReplyDelete
  7. Hi Vanya,
    I am really confused by one thing. If I run the "Apply DateTime range" function for different days (and the same time, 8:00), resulting WHERE clauses use time differently (see examples below). Do you have the same result and if so, do you have an idea why it behaves this way?

    Example:
    WHERE ((createdDateTime>='2010-02-06T22:00:00' AND createdDateTime<='2010-02-08T22:00:00'))

    WHERE ((createdDateTime>='2010-02-07T22:00:00' AND createdDateTime<='2010-02-10T00:08:00'))

    WHERE ((createdDateTime>='2010-02-10T00:08:00' AND createdDateTime<='2010-02-12T00:08:00'))

    (queryRange() returns expected values (from 8:00 to 8:00), but actual query seems to be different.)

    Thanks in advance.

    ReplyDelete
  8. Hmm. On 2 boxes that I tried it outputs exactly the same time as specified (T08:00:00) for all 3 dates.
    Not sure what could be causing this.
    What timezone are you in?

    So, did I understand correctly that before execution it shows the correct date/time in the infolog? And only when actually executing it produces the result from above?

    ReplyDelete
  9. 2 husna yusuf nawawi:
    Your problem is well-known, as well as the solution.
    When doing import into instance B, you have to tick off the "Delete table and class members" check-box under Options in the import dialog.
    This will ensure that any non-existent fields/methods/etc. on the table will be removed during import.

    Note, that you have to be on the same layer as the object being imported in order to delete any of its members.

    ReplyDelete
  10. Vanya: I finally found the source of problem. At first, I simplified my example (to filter a single value, not a range) and run the check for all days in one year. I discovered that values are incorrect (time is ignored) for all days with the number lower than 10 (<10.1., <10.2. etc.).
    If I change date format (in Regional and Language settings in Windows) from 'd.M.yyyy' to 'dd.M.yyyy', it works correctly.

    For completeness: AX2009 SP1, W7 Ent., Czech regional settings

    ReplyDelete
  11. Hmm. I have tried switching the settings to Czech, but it still shows the correct data and time for me. Strange.

    Anyhow, a question - how exactly do you express 31 days in 1 digit 'd'?? :)

    ReplyDelete
  12. Heh, one 'd' means only that the leading zero is supressed. :)
    I am quite sure that the problem is in the date format. BTW AX must be restarted to apply new regional settings.

    ReplyDelete
  13. Oh. OK, so it is actually 2 digits, when needed. good.
    Yeah, I restarted AX, obviously.
    The only difference I can see is that I tried it on AX6, while you tried it on AX2009.
    Maybe there was a bug and it was fixed.
    I will try it out on AX2009 later today and post back with the findings

    ReplyDelete
  14. Hi Vanya

    Nice post. By the way, I'm intrested to that HTML-Style thingy which displays X++ source code as fine as in the current post.
    Can you please give me a mail (as to Anonymous' Request on 27.02.10) what I have to change/implement into Blogger?

    patrick[at]luegisdorf[dot]ch

    Thank you very much and
    Best regards
    Patrick

    ReplyDelete
  15. Hi Vanya,

    We are trying to extract data from the ProdJournalTable based on a date/time range. For example, we have employees whose shift is Monday - Friday 1600 - 0200. We are trying to report on their transactions for the entire shift. Is this possible?

    Thank you !
    Barbara
    barbara.willders@fennerprecision.com

    ReplyDelete
  16. public void init()
    {
    QueryBuildDataSource qbds;
    QueryBuildRange qbr;
    Range queryRange;
    date dateMonday = mkDate(19, 4, 2010);
    timeOfday fromTime = 16*60*60;
    timeOfDay toTime = 2*60*60;
    int i;
    ;
    super();

    for (i = 0; i < 5; i++)
    {
    if (queryRange)
    queryRange += ',';

    queryRange += strfmt('%1..%2',
    queryValue(DateTimeUtil::newDateTime(dateMonday + i, fromTime, TimeZone::GMT_DUBLIN_EDINBURGH_LISBON_LONDON)),
    queryValue(DateTimeUtil::newDateTime(dateMonday + i + 1, toTime, TimeZone::GMT_DUBLIN_EDINBURGH_LISBON_LONDON)));
    }

    qbds = this.query().dataSourceNo(1);
    qbr = qbds.addRange(fieldnum(WorkingTimeTracking, PostedDateTime));
    qbr.value(queryRange);

    box::info(qbds.toString());
    }

    ReplyDelete
  17. Hello Vanya,

    is it a known bug, that when I use a UtcDateTimeField as TitleField, the regional-settings will be used twice?
    In your Form I added the createdDateTime as TitleField2 of the CustTable and then I get the following (Dutch settings):
    - in the database: 08:11:23
    - in the grid: 10:11:23
    - in the titlebar: 12:11:23
    How can i solve this?

    ReplyDelete
  18. Hi, Jack.
    No, this is the first time I see this issue.
    Thanks for posting it here.
    Guess nobody uses DateTime fields as TitleFields.
    I have reported the bug into the Microsoft internal system, and will let you know if there is a workaround.
    I doubt it though, as this is controlled by the kernel.

    ReplyDelete
  19. Hi Vanya
    Great post.
    I'am having a problem about QueryValue range expressions and the UTCDateTime fields. The problem is to use the '==' comparison.
    I Cant seem to find the Value wich is the exact match for a UTCDateTime field value.

    Questions:
    1. Is there a problem with '==' in QueryRange value expressions?
    2. Should the date used in the expression be a UTC value?
    3. Is there a specific dateTime value format to use(Iam using queryValue() perhaps the returned format isnt valid)?
    In the example below the 'valueStr' is used as a range on a UTCdateTimeField.
    The Expression is only a part of the complete expression, but representative. I think...


    str fieldEarliestStart = fieldstr(EGH_WorkOrderTable, EarliestStartDateTime);
    str earliestStartFrom = queryValue(DateTimeUtil::newDateTime(mkDate(03, 06, 2010), str2time("00:00:00"), DateTimeUtil::getUserPreferredTimeZone()));

    ex.
    valueStr = '(';
    valueStr += '(';
    valueStr += '([' + earliestStartFrom + '] == ' + fieldEarliestStart + ')';
    valueStr += ')';
    valueStr += ')';


    Best Regards
    Brian

    ReplyDelete
  20. Hello Vanya

    I have posted a question about the Queryrange value expression, but I forgot my Contact details, sorry... They are as follows:

    Brian G. Frandsen
    brian.frandsen@eg.dk

    ReplyDelete
  21. Hi Vanya,
    How would I get my current time only using DateTimeUtil?
    The old way was:
    time2Str(timeNow(), TimeSeparator::Colon, TimeFormat::Hour24)
    The new way(?):
    time2Str(DateTimeUtil::time(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone())),TimeSeparator::Colon, TimeFormat::Hour24)
    Is that correct?

    ReplyDelete
  22. Hi, Brian.

    Sorry for the so much delayed reply.
    I was on vacation the entire June, and just got back to CPH yesterday.

    Is this still a problem you are facing?
    Obviously, queryValue() is not correct. There is a special format that should be followed.

    If you still struggle with this, I can look it up and e-mail you.

    P.S. By the way, I was not able to use the e-mail you provided above.

    ReplyDelete
  23. Hi, Daniel.
    Not sure if this question is still relevant.
    yeah, it should work the way you wrote it above.

    Note that timenow() is still available, so you can just continue using that.
    Also, check the difference between DateTimeUtil::getSystemDateTime() and DateTimeUtil::utcNow().

    As far as I remember, timeNow() would correspond to the second one, utcNow().

    ReplyDelete
  24. It's good to see that 2009 has a functionality for this. However is there a similar approach for 4.0?

    I'm trying to get a time range. Say get transactions starting from 6am on the 13th to 6am of the 15th.

    ReplyDelete
  25. Well, you have to split it up.
    Specifically, for this example, it can be the following statement:

    (TransDate > 09/13/2010 && TransDate < 09/15/2010) // 14th whole day
    || (TransDate == 09/13/2010 && TransTime >= 6 * 60 * 60) //13th from 6 am
    || (TransDate = 09/15/2010 && TransTime <= 6 * 60 * 60) //15th to 6 am

    ReplyDelete
  26. Hi Vanya! That did the trick :) Now why didn't I think of that... Anyhow, thanks a bunch!

    ReplyDelete
  27. Hey Vanya,

    Its a good tutorial for developers to know more about UTC date time in AX 2009. I have a different questions related to inventory management. Hope u can help me. I need to rename the inventory dimensions ( Serial number , Batch number ) in AX 2009. For renaming the item dimensions ( Color , Size ) , the standard functionality have a rename tab but its not availble for other inventory dimensions. Any idea how to rename the other inventory dimensions ?

    Thanks
    Akash_004@yahoo.co.uk

    ReplyDelete
  28. Well, it is not allowing to rename serial number, batch number, as well as Configuration and all the storage dimensions for a specific reason. All these dimensions have a dedicated functional behavior assigned to them. For example, Serial number control for serial number, product configuration (Product builder) for configuration, etc. So it is not "correct" to rename them.
    You can however create new dimensions, and assign them the required name and functional behavior.

    If you are still not convinced, you can of course proceed with renaming these, i cannot disallow it. It's very simple, actually. All the rename does is update the labels text in the label file. You can do that manually or using a simple console or X++ application.

    ReplyDelete
  29. In AX 4.0 and earlier, when creating user queries (non X++), you can pass the value "D" which would always default to the current date. However, in AX 2009, because of UTCDateTime, this no longer works. Any idea how to achieve the same functionality?

    ReplyDelete
  30. "D" or "T" still works from the UI if you put it into the control -> It will default to today's date and time automatically.

    For using the SysQueryForm (or in AOT queries), you can use the SysQueryRangeUtil class methods.
    The beauty of it is that you can add your own methods to it as well, to be calculated at runtime if specified in the query.
    You can find more information about this class on the web, together with usage examples - basically, you just specify the (()) for the filter value.

    ReplyDelete
  31. (()) should be replaced with (methodName()).
    It got eaten up by the XML parser of blogger.

    ReplyDelete
  32. Hello Vanya,

    about this UtcDateTime in Dynamics AX 2009
    how to minus or add UtcDateTime types ?
    for the example : today - yesterday in UtcDateTime types.

    ReplyDelete
  33. Hi

    Take a look at the DateTimeUtil class.
    It has a number of methods for working with utcDateTime types, including one called DateTimeUtil::getDifference() for getting a difference between 2 utcDateTime values

    ReplyDelete
  34. Hi Vanya, I´ve been counting the number of records fetched using SysQuery::countTotal. It works fine for strings, numbers, etc. But when I do a DateTime filter (using only the date), it returns always zero (despite the filter is alright). Do you know what happens?

    Thanks a lot

    Samuel

    ReplyDelete
  35. Hmm. No, I have not seen that.
    Can you send me a sample xpo or code to try out?
    Thanks

    ReplyDelete
  36. hi Vanya, how to get only time information from utcdatetime data type?

    thx

    ReplyDelete
  37. Eh. It was so easy to get the answer yourself ;)

    DateTimeUtil::time(DateTimeUtil::utcNow())

    ReplyDelete
  38. Hi, Vanya
    I wonder what if user want to use local time.

    User may want local date time to be displayed the same manner anywhere. This is to avoid consolidating conflict.

    If UTC date time is applied to "Invoice date" then users from different time zone may see different values. How to decide which period it falls to?

    American ppl are looking at August financial report, but does the figures included entries that created in Japan on October 1st?

    -Hendrik Liu

    ReplyDelete
  39. Hi Hendrik.
    The user already sees everything in the system in his preferred time zone. (Local time, that is)
    In the database, the data is stored as UTC. So when something is created in Japan in Oct 1st, in the states this data will still be still Oct 1st(or 2nd, depending on the time in Japan). So the August report should go just fine.
    Now, if the situation was reversed - well, naturally, Japan would need to wait until the working times are over in USA before finalizing the report.

    ReplyDelete
  40. What about using in a while select statement?

    How do I do this? I want to get all salesTable records that createdDateTime == today()

    while select salesTable where salesTable.createdDateTime == DateTimeUtil::date(DateTimeUtil::utcNow())

    This does not work however.... :(

    ReplyDelete
  41. You just need to use a range in that case, like below (the date format is actually different, but I just typed it in here. Hope it makes sense)

    while select salesTable
    where salesTable.CreatedDateTime >= 00:00:00 am 11/09/2011
    && salesTable.CreatedDateTime <= 11:59:59 pm 11/09/2011

    ReplyDelete
  42. What's the reason for not providing a simple function that returns the current date and time in local time, something like what System.DateTime.Now from .NET does?
    Why is it necessary that the programmer should go through all the pains of applying timezone offsets etc.?

    Thanks,
    --Horia

    ReplyDelete
  43. That's a good question, thanks Horia.
    I guess the intention was for the DateTime to be used in code, thus not requiring offsets that often.
    I will however write up a DCR for it, to see if we can add a method like this to, let's say, the Global class.

    ReplyDelete
  44. Hi Vanya,
    Thanks for this post ! Very usefull :) I have another DateTimeUtil problem you might know the answer to. Can't seem to find this anywhere. Sounds so obvious that I'm wondering what I'm overlooking.
    DateTimeUtil::parse("XXXXX") will result in an error. I want to prevent this error. Something like:
    if(DateTimeUtil::isValidUTCDateTime("XXXXX"))

    Do you know if something like this is available ?

    Thanks for your time !
    Gerard

    ReplyDelete
  45. Nope, I don't think there's a method like that.
    Can you catch the error?

    ReplyDelete
  46. Nope.... Well... I can catch it, but I wanted to prevent the errorlog that is occuring. If I stumble into something like that isValid method I will let you know.

    Cheers,
    Gerard

    ReplyDelete
  47. Hi Gerard, I have spent some time looking into this.
    I'll create a new post about it, so stay tuned for a solution ;)

    ReplyDelete
  48. Think something went wrong with the previous post..... Anyway....
    Great ! thanks a log Vanya. Very kind of you :)

    Cheers,
    Gerard

    ReplyDelete
  49. Let me know if it works for you:
    http://kashperuk.blogspot.com/2012/02/tutorial-determine-if-string-is-valid.html

    ReplyDelete
  50. Thanks Vanya ! This works great. Still have a problem with AIF and implenting this (seems like AIF raises the error before it can be catched), but that's a completly different issue :)

    Thanks again !
    Cheers,
    Gerard

    ReplyDelete
  51. Hi Vanya,
    how can i set the timeOfDay value in .NET. I am consuming AIF service in .NET, but i couldn't able to set the timeOfDay field of ProjJournalTrans field correctly. It always goes as 12:00 in the record. Please help in this regard.

    ReplyDelete
  52. Well, timeOfDay is really an integer, showing the number of seconds from the start of the day
    So if you want to set it to, say, 4 pm, you should set the value to 60 * 60 * 16

    ReplyDelete
  53. Please see the code below i have wirrten this code on Report level on executesection now every thing is ok but when i am using select and where using for geting the record from purchtab but when i am counting the recid with date it showing 0 without date it is ok please see the code and help please




    public void executeSection()
    {


    PurchTable purchtab;
    purchid purhcaseorder;
    int totalcount;
    QueryBuildDataSource qbd;
    QueryBuildRange range;
    int cnt, i;
    str tempdate;
    utcdatetime tempdate1;

    ;
    sr = sr;

    cnt = queryRun.query().dataSourceTable(tablenum(PurchTable)).rangeCount();

    range = queryRun.query().dataSourceTable(tablenum(PurchTable)).range(2);

    tempdate = range.value();
    fromdate1 = str2datetime(tempdate ,123);
    info(strfmt("String date is %1 and Fromdate is %2",tempdate,fromdate1));

    select count(recid) from purchtab where
    purchtab.InventLocationId == purchtable1.InventLocationId &&
    Purchtab.OrderAccount == purchtable1.OrderAccount &&
    Purchtab.PurchaseType == purchasetype::Purch ;

    info(strfmt("Loc is %1 OrderAcount is %2 Purchtype is%3 ",purchtable1.InventLocationId,purchtable1.OrderAccount,purchtable1.PurchaseType));


    if (purchtab.recid == 1 )

    {

    element.disableBody();

    }


    info(strfmt("Purch rec id is %1 and Fromdate is %2",purchtab.recid,fromdate1));




    super();
    }

    ReplyDelete
  54. I am not sure what to say here.
    Can you show me the code where the problem is, maybe in way of comments?

    Looking at the above, there are some strange lines, but the query itself should be OK, you get a count of purchase orders for the currently displayed order account that also have the default warehouse specified and equal to the one on your currently displayed purchase order header. I don't see any date filters.

    tempdate1 and fromDate1 should be the same looking at the above code. is that not the case?
    Are you sure the filter you put on the query is done correctly?

    What kind of query do you see if you do info(queryRun.query().dataSourceTable(tablenum(PurchTable)).toString()); ?

    ReplyDelete
  55. Hi Vanya,

    I have a concern related to the "Daylight saving time" set up in the windows time zone. Where do I find this setup in AX 2009 (MorphX)? How will I get to know in AX that this set up of DST has been enabled? It would be really great if you could help me out in this regard..

    ReplyDelete
  56. Well, I am not aware of a method similar to this in X++.
    But you can always use the .NET version, I guess.
    http://msdn.microsoft.com/en-us/library/bb460642.aspx

    Question is: why would you ever want that information?
    All you care about is that the date is correct.

    ReplyDelete
  57. Actually, there is a requirement to check if the "Daylight saving time" is enabled. And if it is enabled, then the datetime must be displayed as for example UTC-2:00, otherwise it must be displayed as UTC-3:00. I am unable to find how do we get that set up in X++ :(

    ReplyDelete
  58. Well, Daylight saving time is considered by the kernel when it displays the DateTime field in the User Preferred time zone.
    You can apply the time zone offset based on the user preferred time zone as shown in the blog post above.

    ReplyDelete
    Replies
    1. Thanks a lot Vanya for your support. I will try to apply the time zone offset based on the user preferred time zone. Just wanted to find a simpler way for it if it existed.

      Delete
  59. Hello Vanya
    I'm learning to make job in AX 2009. I'm learning and looking for different days in format utcdatetime.
    for example: I will calculate the length of day between salesparmtable and custtpackingslipjour

    custpackingslipjour.createdDateTime = 12/19/2012 08:28:32
    SalesParmTable.createdDateTime = 12/14/2012 10:18:12
    then the result is = 5 days

    I write the following code

    display str totdays()
    {

    CustPackingSlipJour CustPackingSlipJour;
    salesparmtable salesparmtable;
    ;

    select CustPackingSlipJour
    where CustPackingSlipJour.SalesId == salestable.SalesId ;


    select salesparmtable
    where salesparmtable.SalesId == salestable.SalesId ;


    return(strfmt("%1",DateTimeUtil::getDifference(custpackingslipjour.createdDateTime,SalesParmTable.createdDateTime))) ;

    }




    Best regards

    Didik,
    diecorecore@gmail.com
    die_core2@yahoo.com

    ReplyDelete
    Replies
    1. Hi Didik, I am not sure I understood, if that was supposed to be a question, or just a comment on what you've been able to do after reading the post.

      Delete
    2. Hi Vanya
      The question is How to get different Date in UtcDateTime. I mean, with the code I wrote, but the result was not as I wanted. I want the result is the date minus the date ((12/19/2012 08:28:32) - (12/14/2012 10:18:12) = 5)).
      but the result of my code is always in seconds. where is the fault code that I created. And how is the correct code so that I can get the difference date in UtcDateTime

      Regards
      Didik

      Delete
  60. The value you are getting is the number of seconds in the difference between the two utcDateTime arguments. If you divide it by 3600 and 24, you'll get the exact number of days (because the time is not the same in both).
    If you are only interested in the date part, you can use the date() method in DateTimeUtil class to retrieve that part only, as shown below:

    static void Job1(Args _args)
    {
    utcDateTime date1 = DateTimeUtil::newDateTime(systemDateGet(), 3006);
    utcDateTime date2 = DateTimeUtil::newDateTime(systemDateGet() - 5, 9400);

    info(strfmt("%1 days, exactly", DateTimeUtil::getDifference(date1, date2) / 3600 / 24));
    info(strfmt("%1 days, only days", DateTimeUtil::date(date1) - DateTimeUtil::date(date2)));
    }

    ReplyDelete
  61. Oke Vanya Thanks..
    I'll try it first..


    regards

    ReplyDelete
  62. Hello Vanya
    First of all many thanks for your detailled description.
    I have a question concerning the 'd' (for today). This is often used for reports.
    We would need a smiliar function 'd-3'. I mean today minus 3 days.
    Do you know, if this is possible? And how it can realised if it's possible.

    Thank you very much in advance.
    Roli

    ReplyDelete
  63. Hi Roli, I am not sure where exactly you want to do this, so the reply is going to be generic.
    There's the DateTimeUtil::addDays() which you can use to create the 'd-3' date, setting it in the query range.

    There's also a SysQueryRangeUtil::day(), which seems to do exactly what you want (you specify 3 as the argument). These functions you can use in the extended range form as well.

    ReplyDelete
  64. Hi Vanya
    Thank you very much for your fast answer.
    It would be great, if 'd-3' can be used by the users for calling reports, for filtering etc. not by code.
    It should works AX-wide.

    Here an example:
    We have reports, which must have the date ..14.1.2013 if the report is started at the 17.1.2013(this is 'd' minus 3 days)
    If the report is started next Monday, the 21.1.2013, then the report should list all until the 18.1.2013.

    The selection-criterias are stored in the SYSLastValues. If the SYSLastValues saves 'd-3' the user can start the report without changing the date-range.

    I hope you understand, what i mean.

    Greetings
    Roli

    ReplyDelete
  65. Hi Roli, so like I said above, you want to use the SysQueryRangeUtil class for this.

    Either the day() method, or the lessThanDate() method, it's not clear from the above, whether you want the specific date, or all dates less than that date

    Read the below to see what I mean:
    http://axdaily.blogspot.co.uk/2010/02/sysqueryrangeutil-class.html
    https://community.dynamics.com/product/ax/axtechnical/b/axaptavsme/archive/2012/11/06/sysqueryrangeutil-class-use-method-name-as-query-criteria-reports.aspx

    ReplyDelete
  66. Hi Vanya thanks again i will study the stuff under the websites you mentioned.
    Have a nice day.

    Roli

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you