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

All responses (12)

Answers (1)

Suggested answer

Ronny 80 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)

you can use :

return strFmt("year(%1)",

SysComputedColumn::returnField(

tableStr(GlkVwSalesInvoice),

identifierStr(CustInvoiceJour_1),

fieldStr(GlkVwSalesInvoice,InvoiceDate))

);

in your computed columns
Martin Dráb 233,396 Most Valuable Professional 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)

AshishSpencer, this this forum is about "How to get Year and month from Date field of a table using Computed column" and it's already answered. Your question seems be about something else, therefore please create a new thread and explain your problem in detail there.

Also, notice that dates are not saved as strings; they don't have any specific string format, so there is no need for converting from one string format to another. If you do store a date as a string, please fix this bug in your data model and use an appropriate type (date or utcDateTime) instead.
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
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?
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....
Suggested answer

Martin Dráb 233,396 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.
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'
Martin Dráb 233,396 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.
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
Verified answer

Martin Dráb 233,396 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.