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:

Answer 1

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);