Dynamics Ax 2009 using the DateTimeUtil

Since i’m getting a lot of google hits on my Dynamics Ax โ€“ workdays to days post, i’ve decided to blog some more about it. The DateTimeUtil class is actually a wrapper of the .NET DateTime class.

A first thing to remember when using UtcDateTime EDT’s is that it is stored like the name says as Coordinated Universal Time.

The controls on the form will translate the DateTime to the timezone of the client. Now keeping this in mind is very important when mixing date, time and datetime controls. The following example will make it more clear.

The first field is a UtcDateTimeEdit control with a data method that returns DateTimeUtil::UtcNow().

DateTime debugger

As you can see the time is 08:58, but the first control on the form shows 10:58. This is correct because my client timezone is (GMT+01:00) Brussel, Kopenhagen, Madrid, Parijs and it’s summer time.

The second field is a TimeEdit control with a data method that returns DateTimeUtil::time(DateTimeUtil::utcNow()), this isn’t correct because it will always return the time in the UTC timezone and the control will not translate it to the correct timezone.

The third field is another TimeEdit control with a data method that returns TimeNow(), this is correct because the TimeNow method will also apply the client/server (depending on the tier) timezone.

this also applies to field in a table.

Datetime example
Datetime example

Another way to use Time controls and the DateTimeUtil is using the applyTimeZoneOffset method.

UtcDateTime ret;
;
 
ret = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(),TimeZone::GMTPLUS0100BRUSSELS_COPENHAGEN_MADRID);
 
return  DateTimeUtil::time(ret);

but this is a lot of code for a rather more simple thing ๐Ÿ™‚

A second thing to remember, when querying with a date on UtcDateTime fields make sure you select the whole day from 00:00:00 to 23:59:59.

For this I like to implement a method on the Global class, it’s keeps you’re queries cleaner ๐Ÿ˜‰ It works the same way as all .NET developers use, Add a with time 0 and subtract a second.

static UtcDateTime dateCompare(date      _date,
                                  boolean   _toDate = false)
{
    UtcDateTime ret;
    ;
 
    if(_toDate)
    {
        ret = DateTimeUtil::addSeconds(DateTimeUtil::newDateTime(_date + 1,0),-1);
    }
    else
    {
        ret = DateTimeUtil::newDateTime(_date,0);
    }
 
    return ret;
}

So you’re query looks like this.

Date        currectDate = SystemDateGet();
SalesLine   salesLine;
;
 
while select    salesLine
where           salesLine.createdDateTime >= dateCompare(currectDate)
&&              salesLine.createdDateTime  <= dateCompare(currectDate,true)
{
    //Do something
}

Dynamics Ax modifying CreatedDateTime

For testing purposes with the MRP we needed to modify the createdDateTime fields in Dynamics Ax 2009. Since these are system fields we needed a workaround.

if(isRunningOnServer() && CurUserId() == "Admin")
{
            new OverwriteSystemfieldsPermission().assert();
 
            salesLine.overwriteSystemfields(true);
                                                                             // "YYYY/MM/DD HH:MM:SS"
            salesLine.(fieldnum(SalesTable,CreatedDateTime))    = str2datetime( "2010/04/03 11:00:00" ,321 );
            salesLine.doInsert();
            salesLine.overwriteSystemfields(false);
 
            CodeAccessPermission::revertAssert();
}

Remarks:

  • Make sure that it is running on the server tier
  • It only works on insert NOT on update

Since this code is pretty exotic and you don’t want to release this to a production environment we eventually didn’t use this but ran some sql jobs, but this shows that it is possible.

edit
Easy does it ๐Ÿ˜‰

DECLARE @DATAAREAID nvarchar(4)
DECLARE @SALESID    nvarchar(20)
DECLARE @DATETIME   datetime
 
/* EDIT THE FIELDS BELOW */
 
SET     @DATAAREAID     = 'CEU'
SET     @SALESID        = '00003352_058'
SET     @DATETIME       = '01/01/98 00:00:00.000'
 
/* DO NOT EDIT HERE */
 
UPDATE  dbo.SALESTABLE
SET     CREATEDDATETIME = @DATETIME
WHERE   SALESID         = @SALESID
AND     DATAAREAID      = @DATAAREAID;
 
UPDATE  dbo.SALESLINE
SET     CREATEDDATETIME = @DATETIME
WHERE   SALESID         = @SALESID
AND     DATAAREAID      = @DATAAREAID;

Dynamics Ax workdays to days

Ever had to calculate the number of days starting from a number of workdays and even deal with holidays.
A pretty straightforward example using the DateTimeUtil class and DayOfWk method.

static void WorkDaysToDays(Args _args)
{
    int                 workDays    = 10;
    int                 days        = 0;
    date                startDate   = systemDateGet();
    CalendarId          calendarId  = CompanyInfo::find().ShippingCalendarId;
    ;
 
    if(calendarId)
    {
 
        while(workDays > 0)
        {
            if(dayofwk(startDate) != 6 && dayofwk(startdate) != 7 && WorkCalendarDate::isDateOpen(calendarId,startDate))
            {
                workDays--;
                days++;
            }
            else
            {
                days++;
            }
            startDate = DateTimeUtil::date(DateTimeUtil::addDays(DateTimeUtil::newDateTime(startDate,0),1));
        }
        info(strfmt("Workdays = %1, Days = %2",workDays,days));
    }
    else
    {
        error("No calendarId found");
    }
}