Friday, May 23, 2008

Dynamics AX 2009 RTM download

Dynamics AX 2009 RTM has been released. Follow the link below for more information.

Download link (Partner source login required)

Tuesday, May 13, 2008

Dynamics AX - UTC time to local time

A problem that I encountered when doing data conversion in AX is to grab a DateTime value in SQL and convert it to local time.

I've figured out the following way, although it's probably not the smartest.

1) Get the DateTime value from SQL as a string.
    In the T-SQL statement write the following:

    CAST(TableName.DateFieldName AS char(30))

    so that the dateTime values becomes a string.

2) The result string has a fixed length for both the 'date' and the 'time' (Notice the 'time' part doesn't include seconds). By doing string manipulation we can eventually get the 'date' and 'time' seperately.

e.g.
datePart            = strLRTrim(subStr(dateStr,1,12));
datePart            = this.trimDateStr(datePart);
timePart            = strLRTrim(substr(DateStr,13,strlen(DateStr)));
timePart            = subStr(timePart,1,strlen(timePart) - 2) +
                      ":00" +
                      substr(timePart,strlen(timePart) - 1,2);
tmpDate             = str2date(datePart,213);
tmpTime             = str2time(timePart);

3) Now that you get the UTC 'date' and UTC 'time'. The way to transform it into local date time is make use of some WINAPI methods. (Only available in DAX 4.0. However, you can copy and paste these methods into DAX 3.0 and they'll work too)

e.g.
static container dateTime2LocalTZDateTime(TransDate _date, TimeHour24 _time)
{
    Binary      systemTime;
    container   c;
;
    systemTime  = WinAPI::dateTime2SystemTime(_date,_time);
    systemTime  = WinAPI::systemTimeToTzSpecificLocalTime(systemTime);
    c           = [WinAPI::systemTimeToDate(systemTime),
                   WinAPI::systemTimeToTimeOfDay(systemTime)];

    return c;
}

I am interest to know if there's a better way for getting the Date and Time from a dateTime field, for the way I do it here isn't ideal I'd say.

OverwriteSystemFields(boolean)

I need to do some sort of manual EDI and need to assign a specific value to the system CreatedDate field. I remember about this property method but always forgot the implementation. The trick is we can't do a simple system field assignment. So I'll put it here to remind myself.

CustTable custTable;

custTable.overwriteSystemFields(true);
custTable.(fieldNum(CustTable,createdDate)) = [custom date];

custTable.insert();