In x++ list of Date

(0) Share

Report

Posted on by Riyas ahamed F

1,215

Hi guys,

i want to in the LedgerJournalTrans list of posting Date get in only "January"

below my code

   LedgerJournalTrans journalTrans;
   date currentDate = DateTimeUtil::date(DateTimeUtil::utcNow()); 
   SysDate listOfYearJanuary = prevYr(prevYr(dateStartYr(currentDate))); 
    
   while select firstOnly10 journalTrans 
       where journalTrans.TransDate == listOfYearJanuary
    {
      info(strFmt("%1", journalTrans.TransDate));
    }

in this above code i can get only specific year January but i want to output below and how to get list of January date in x ?

result ;

1/1/2018

1/3/2018

1/4/2018

1/1/2019

1/3/2019

1/2/2020

I have the same question (0)

All responses (11)

Answers (3)

Suggested answer

nmaenpaa 101,162 Moderator on at

Like (1)

Report

You can't do that with only one condition in your where clause.

You need to first gather a list of dates that you want to use in your query. 1.1.2020, 31.1.2020, 1.1.2019, 31.1.2019 and so on, for as many years as you want to search for.

Then use all these dates as criteria for your query. Something like "give me records where date is between begin and end of Jan 2020 OR begin and end of Jan 2019 OR..."

You can use SysDa or Query class to create a query with dynamic amount of ranges. With select statement it's not possible.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,095 Super User 2025 Season 2 on at

Like (0)

Report

Hi Riyas,

What is the exact business requirement here? Wouldn't be a Power BI report an alternative approach?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (2)

Report

An efficient solution would be creating a view with a computed column returning the month of each date (MONTH(TransDate)).

Filtering by this column would be trivial.

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Thnaks for your replay Nikolaos Mäenpää sir,

give us a Query example for that ?

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Thanks for your replay Martin Dráb sir,

it is possible to achieve in computed column ?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

Yes, I suggested that because it's possible.

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (1)

Report
Martin's approach is better.

But here's an example of the Query approach - just add as many ranges as you wish.

Query myQuery = new Query(); QueryRun qr; LedgerJournalTrans ledgerJournalTrans; QueryBuildDataSource qbds = myQuery.addDataSource(tableNum(LedgerJournalTrans)); date dateFrom = str2date('01-01-2020', 123); date dateTo = str2date('31-01-2020', 123); str rangeStr = strFmt('((%1.%2 >= %3) && (%1.%2 <= %4))', qbds.name(), fieldStr(LedgerJournalTrans, TransDate), date2StrXpp(dateFrom), date2StrXpp(dateTo)); qbds.addRange(fieldNum(LedgerJournalTrans, TransDate)).value(rangeStr); qr = new QueryRun(myQuery); while (qr.next()) { ledgerJournalTrans = qr.get(tableNum(ledgerJournalTrans)); }

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Many thanks Nikolaos Mäenpää sir,

but without hard code how we can get list of 'Jan' date are any years.

date dateFrom = str2date('01-01-2020', 123);

date dateTo = str2date('31-01-2020', 123);

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

You should quite easily be able to get dates for January 2020. Do you think you can do that?

Once you can do that, you should quite easily be able to get dates for January 2019, right?

And January 2018, and so on. You only need to decide how many years you need.

Was this reply helpful? Yes No
Verified answer

udaY-ch 5,133 on at

Like (1)

Report
Hi,

hope this helps

static void loopTransbyMth(Args _args) { int startMth = enum2int(MonthsOfYear::January); TransDate currentDate = today(); TransDate startDate = 1\1\2019; //dateStartyr(currentDate); you can add prevYr function too TransDate endDate = 28\2\2020; //dateEndyr(currentDate); LedgerJournalTrans journalTrans; while select journalTrans where journalTrans.TransDate >= startDate && journalTrans.TransDate <= endDate { if (mthOfYr(journalTrans.TransDate) == startMth) { info(strFmt("%1", journalTrans.TransDate)); } } }

But as martin suggested, you need to create the view and add the month as comuptedcolumn to make the filter appropriate to get faster results.

Uday

Was this reply helpful? Yes No