CODEWITHSUNDEEP
axaptadynamics-ax-2012dynamics-ax-2012-r3
Nico
Nico

Reputation: 1197

Return UTCDatetime in computed Column

How to return a UTCDateTime in a computed column in an AX view?

I have two fields (date and time); time is stored as int (seconds from midnight) internally. To remove duplicates I need to convert this to a UTCDateTime and am trying to achieve this in a computed column.

Comparing date and time does not work, because 1.1.15 24:00 and 2.1.15 0:00 are different in the database (1.1.15; 86400 and 2.1.15;0), although it's finally the same timestamp.

It would even be easier to calculate the DateTime in the select statement, but also something like this

while select
        count(RecId)
        , sum(delta)
        //, TransDate
        //, timeOffset
        , DateTimeUtil::addSeconds(DateTimeUtil::newDateTime(myTable.TransDate, 0, DateTimeUtil::getClientMachineTimeZone()), myTable.timeOffset)
        , timestamp
        , WrkCtrid
        , calendarId
    from
        myTable
    group by
        WrkCtrid
        //, TransDate
        //, timeOffset
        , DateTimeUtil::addSeconds(DateTimeUtil::newDateTime(myTable.TransDate, 0, DateTimeUtil::getClientMachineTimeZone()), myTable.timeOffset)
        , timestamp

does not work.

Upvotes: 0

Views: 2772

Answers (2)

Spencer Kershaw
Spencer Kershaw

Reputation: 480

I don't quite understand what your asking with comparing dates and times, as the situation you outlined is handled by the dateTimeUtil (X++):

int maximum = 86400;
int minimum = 0;
date d1 = 01\01\2015;
date d2 = 02\01\2015;

utcDateTime one;
utcDateTime two;

one = DateTimeUtil::newDateTime(d1, maximum);
two = DateTimeUtil::newDateTime(d2, minimum);

info(strFmt("%1,   %2", one, two));

Both of the dates outputted by this code are the same: 2/1/2015 12:00:00 am (dd/mm/yyyy).

Therefore, we could just grab the date and time fields using SysComputedColumn::returnField, and plug them into DateTimeUtil::newDateTime natively, and return the dateTime as a string in the computed column method using strFmt.

Upvotes: 0

Maxim Lazarev
Maxim Lazarev

Reputation: 1254

Suppose you have a table MyTable with fields TransDate of type Date and timeOffset of type int.

Create a view, MyView, and add this table to datasource. Create new utcDateTime computed field, DateAndTimeOffset.

Add following method to the view:

public static server str calculateDateTime()
{
    return "DATEADD(ss,timeOffset,TransDate)";
}

Set ViewMethod property of computed field to calculateDateTime.

Now you can group by the new computed field in the select statements:

MyView myView;
while select myView group by myView.DateAndTimeOffset
{
    info(strFmt('%1',myView.DateAndTimeOffset));
}

Here is a good article about computed fields in views.

Upvotes: 1

Related Questions