Datepart function error in Microsoft Dynamics SQL

Question

I am currently trying to perform a SQL query in Microsoft Dynamics AX2012, to output a year from delivery date by using a DATEPART function.

I have created a class "CustRerportDemo" in the AOT, and while attempting to perform a query, which is to query out only the year from the field "deliverydate" in the table "SalesTable". I encountered an error that prompts:

Variable Datepart has not been declared

I understood that datepart is a function call in SQL and shouldnt need to be declared. Hence, I am wondering why and how to rectify this issue? I am only just trying to show the year from the delivery date.

Therefore, if the date is 13/06/2016, the resultant query result will just be 2016 or 16. I have attached the following code. Please help.

public void processReport()
{
CustTable custTable;
SalesTable salesTable;

//select all customers
while select * from custTable
{
    //clear the temporary table
    custReportRDPTmp.clear();
    //assign customer account and name
    custReportRDPTmp.CustAccount = custTable.AccountNum;
    custReportRDPTmp.Name = custTable.name();
    //select count of invoiced sales order of customer
    select count(RecId) from salesTable
    where salesTable.CustAccount == custTable.AccountNum
    && salesTable.SalesStatus == SalesStatus::Invoiced;
    custReportRDPTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId);
    //New Column to display PaymentMode
    select PaymMode
    from salesTable
    where salesTable.PaymMode == custTable.PaymMode;
    custReportRDPTmp.Payment = SalesTable.PaymMode;
    //New Column to display SalesAmountTotal by drawing from a different table using a JOIN statement
    select smmSalesAmountTotal
    from salesTable;
    custReportRDPTmp.SalesAmt = salesTable.smmSalesAmountTotal;

    //New Column to display month from delivery date
    select DATEPART("yyyy", DeliveryDate) as year
    // To extract  a single value for year and month from DeliveryDate in SalesTable
    from salesTable

    /* where payment in (select count(payment) from salesTable
     where salesTable.CustAccount == custTable.AccountNum
    &&*/

    //insert in temporary table buffer
    custReportRDPTmp.insert();
}
}

Edited code:

select firstOnly DeliveryDate
from salesTable
where salesTable.CustAccount == custTable.AccountNum;
//Get Year from date
custReportRDPTmp.DateTimeStamp = year(salesTable.DeliveryDate);

Result as shown:

As Seen under the DATETIMESTAMP: REturn value of 0

score 2 · Accepted Answer · answered Jun 13 '15 at 13:46

2

X++ select statement is not SQL and thus you should not assume that any or all SQL functions is available in X++; they are not!

There are date functions, the one you search is called year.

It does not belong in the select though:

select firstonly DeliveryDate from salesTable;
y = year(salesTable.DeliveryDate);

answered Jun 13 '15 at 13:46

Jan B. Kjeldsen

17,817
5
32
50

Will I need to create a table by the variable of "y" to store the field for year? Thanks for your help – developer Jun 15 '15 at 01:11
furthermore, wouldn't `firstonly` mean that only the first data in the column be selected? – developer Jun 15 '15 at 03:47
You will need a variable (`y` or whatever) to hold the value. You will need a table field only, if you need to group on the value. You can even format the date field in the report to only show the year. The `firstonly` selects one record only, you should do the select you need. – Jan B. Kjeldsen Jun 15 '15 at 07:38
Yes, 'firstonly' only selects one record, however, I understand it as it will only return one query record from the top stack. How about other records that is being matched with different i.ds? – developer Jun 15 '15 at 09:46
Use a `while select salesTable where ...` to select matched orders. – Jan B. Kjeldsen Jun 15 '15 at 10:19
sorry, I don't really get it – developer Jun 15 '15 at 14:50
I advice you to study the X++ language and examples of its use. Explaining `while select` here would be beyond the scope of the question. – Jan B. Kjeldsen Jun 15 '15 at 15:44
I have used the following method suggested by you, however returned query values are all 0 in the report. why is that so – developer Jun 16 '15 at 02:19
I guess your `DateTimeStamp` is of type `DateTime` instead of `int`. In that case transfer the hole date and do the formatting in SSRS. – Jan B. Kjeldsen Jun 16 '15 at 06:39
The type is actually in int. Furthermore, if I were to specify the typr as a str for DATETIMSTAMP, I have done this for the following code: 'custReportRDPTmp.DateTimeStamp = int642str(year(salesTable.DeliveryDate)); ' – developer Jun 16 '15 at 07:00
Change the table to not temporary, then table browse the table. I have previously problems with new fields, a restart of the report server might do the trick. – Jan B. Kjeldsen Jun 16 '15 at 14:55

Datepart function error in Microsoft Dynamics SQL

1 Answers1