Sunday, March 12, 2017

Computed column date to get max day on month for AX 2012 R3

public static server str getMaxDateTransDate()
{
    #define.ViewName(InventValueTransViewSumLoc)
    #define.DataSourceName("InventValueTransView")
    #define.FieldDisplayValue("RecID")

    str sWhereClause1;
    DictView dictView;
    SysDictTable dicTable;

    // Construct a DictView object for the present view.
    dictView = new DictView(tableNum(#ViewName));
    //Construct a table object for the account table
    dicTable = new SysDictTable(tableNum(InventValueTransView));

    // Create the where clause
    sWhereClause1 = DictView.computedColumnString
    (
        'InventValueTransView',
        fieldStr(InventValueTransView, RecId),
        FieldNameGenerationMode::WhereClause
    );

    return strFmt
    (
        //'select year(TransDate) from %1 where %2 = %3',
        'SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,TRANSDATE)+1,0))',
        dicTable.name(DbBackend::Sql),
        dicTable.fieldName(fieldNum(InventValueTransView, RecId), DbBackend::Sql),
        sWhereClause1
    );
}

No comments:

Post a Comment