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.

No comments:

Post a Comment