createdDateTime in a WHERE Clause in while select statement

(0) Share

Report

Posted on by Community Member

Hi Guys,

I been working on an SSRS report where I need to get some data on the Product Receipt Table.

My only problem is, how can I query the createdDateTime field in a while select statement and include the createdDateTime in the WHERE clause as range.

I already tried the below syntax without any errors when compiling, but when executing the report, it shows nothing.

Or can to tell me the alternative to get the same result?

I don't know what else to do, I am just new in the AX development.

Thanks in advance.

    utcDateTime             utcFromDate, utcToDate;
    date                    dTFrom, dTTo;
    TimeOfDay               tFrom, tTo;
    
    contract = this.parmDataContract();
    
    dTFrom   = contract.parmFromDate();
    dTTo     = contract.parmToDate();
    
    tFrom    = str2time("12:00:00 am");
    tTo      = str2time("11:59:59 pm");
    
    utcFromDate = DateTimeUtil::newDateTime(dTFrom, tFrom, DateTimeUtil::getUserPreferredTimeZone());
    utcToDate   = DateTimeUtil::newDateTime(dTTo, tTo, DateTimeUtil::getUserPreferredTimeZone());

    while select sum(Ordered), sum(Qty), sum(ValueMST) from vendPackingSlipTrans
        group by CostLedgerVoucher, createdDateTime
        where vendPackingSlipTrans.createdDateTime      >= utcFromDate
            && vendPackingSlipTrans.createdDateTime     <= utcToDate
            && vendPackingSlipTrans.Qty                 != 0
            && vendPackingSlipTrans.Qty                 >= 1
        join vendPackingSlipJour
        group by PurchId, PackingSlipId, OrderAccount
            where vendPackingSlipJour.OrderAccount      == contract.parmVendAccount()
                && vendPackingSlipJour.PackingSlipId    == vendPackingSlipTrans.PackingSlipId
                && vendPackingSlipJour.PurchId          == vendPackingSlipTrans.OrigPurchid

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Martin Dráb 238,193 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

There is no syntactic problem regarding createdDateTime in the WHERE clause, although grouping by a datetime field sounds like a pretty bad idea.

If the query doesn't return anything, it means that there are no records meeting all conditions. Nevertheless your statement that the report shows nothings suggests that you're debugging a wrong place. First review the query to see if it works. If using it an a report makes no sense; it can't work and it will; just make debugging more difficult. If the query works but the report shows nothing, you have a problem with the report and not the WHERE clause.

If you're sure that it's about conditions for createdDateTime, debug your code to be sure that your utcFromDate and utcToDate contain what you expect and then look at the actual data in database.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!
Have you tried to debug it and see what value gets assigned in the variables?

You may try to replace your from and to dates with a fixed value for a start, instead of using the helper functions, and see if that gives back the correct results as a first step. You may define them as constant values like this:

utcFromDate = 2017-01-01T00:00:00; utcToDate = 2017-04-20T23:59:59;

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

It's because .createdDateTime does not return a utc date (I.E. 1/1/1990 00:00:00). When using the DateTimeUtil you are working with a different format (I.E. 1-1-1990T00:00:00). It cannot do a comparison between two different types.

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

Like (0)

Report
Copy link

Link copied!

That's a completely wrong statement.

The data type createdDateTime is utcDateTime and it can be compared with other utcDateTime values.

This data types don't use string formats at all. Therefore statements like "When using the DateTimeUtil you are working with a different format (I.E. 1-1-1990T00:00:00)" are not true. You're working with utcDateTime types, not strings. You get a string only when you decide to convert utcDateTime to string, and that's when you can choose a string format.

Also, it's not true UTC is about a string format. UTC is about timezones. For example, it's 6 AM at my place, but 4 AM in UTC. You can use any format when converting it to string.

Was this reply helpful? Yes No