Microsoft Dynamics AX (Archived)

How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

(0) Share

Report

Posted on by Community Member

Hi Team,

I have created two computed colums in myView ( having table custpackingsliptrans table as datasource) month and year.

For month i have written one static method to link my computed colum. in method first i am getting date field

using

str deliverydate;

deliverydate = SyscomputedColum::getReturnField(viewname,datasourcename,fieldname );

return int2str(year(str2date(deliverydate,213))).

but its not giving correct year for each row in a view.

when i just return the same field using return deliverydate; i am getting date like jan 2 201

year is not complete.... not getting the point why same date field i am returning using getreturnfield

why date field "23/2/2011" coverted to Feb 23 201 ( year is not correct, even date format got change ).

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Florian Hopfner 2,457 on at

Like (1)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

You might want to (re)read the documentation for computed columns. Basically the method of the computed column should return a string that becomes part of the SQL-statement. So instead of returning the year of a date, you would have to return a string with some SQL syntax that will transform the date value to a year value.

See also Walkthrough: Add a Computed Column to a View [AX 2012]

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Thanks for your response.

I am trying to return string value only. My objective is to get Year from DeliveryDate Field and put that year in a separate computed column field.

Currently issue which i am facing is format which i am getting of field delivery date. using below function

str deliveryDate;

deliveryDate = SyscomputedColum::getReturnField(viewname,datasourcename,fieldname );

when i used above output in computed colum format is like below..

If Delivery date is = 23/2/2011

then computed colum showing = Feb 23 201 ( From this value where year itself is not coming properly how to fetch year )

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (2)

Report
RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Again, the purpose of computed columns is not doing the calculation. They return SQL code that SQL Server executes to calculate values.

First think about what SQL code you're trying to build. E.g.

SELECT YEAR(MyDateField) FROM MyTable WHERE MyTable.MyId = SomeOtherTable.Id

Then implement a computed column in AX returning the query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Hi, I am facing the same issue with Anil....

Martin, i understand your explanation, but how to put the SQL code into View method?

From Tjien Ping

Was this reply helpful? Yes No
Martin Dráb 236,394 Most Valuable Professional on at

Like (1)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

That's what computed columns are all about, as I just tried to explain.

If the MSDN documentation isn't enough, you can find additional discussions and examples on internet. One of them is my article about Subqueries in views.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Thanks Martin for you response. Your hint gave me some idea to resolve my problem.

Actually i mixed two issues so that is the reason i couldn't able to convey actual issue.

Actual issue is Format of date field when we are getting it from view to use in select statement in static method.

Apart from format even year is also missing in new format. see below code.

public server static str getYear()

{

str gettingSameDeliveryDate;

// below code should give me view field Deliverydate value in string format

gettingSameDeliveryDate = SysComputedColumn::returnField(

tableStr(Anil_CustPackingSlipTransView),

identifierStr(CustPackingSlipTrans_1),

fieldStr(CustPackingSlipTrans, Deliverydate));

// now say i am returning same value for my new computed column

return gettingSameDeliveryDate

}

Output

View deliverydate field value = 23/2/2011

Computed colum field value = Feb 23 201

But for new computed column ( string type ) format is Feb 23 201 ( year is not complete it is missing ) so i cant use this return type. it should return exact value in string '23/2/2011'

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (1)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

If you want a date value, use a date computed column, not a string one.

If you want a string, use a longer string field.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Yes you are right... i was trying to achieve in different way by getting a year in string variable, then taking the year from that string then return. but better way to use select year(deliverydate) from custpackingsliptrans where recid = "getrecidfromviewcolum" its working now....

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Can you please let us know the code that you wrote in your static method which you linked to your computed column for extracting year?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get Year and month from Date field of a table using Computed colum ( one for month and one for year)

Hi Anil,

Can you please provide the explanation for the method to convert

02/23/2001 format to February 23 2001

That will be really helpful

Regards

Was this reply helpful? Yes No