web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance forum / return records that go...
Finance forum
Suggested Answer

return records that got created in certain dates

(1) ShareShare
ReportReport
Posted on by CU22120935-0 252
Hi,

I'm trying to look for records in a certain period inside my sysOperationFramework class. But the while loop is not being entered:

while select table1
            where table1.InventLocationId == 'XX'
                && DateTimeUtil::date(table1.CreatedDateTime) >= _contract.parmFromDate()
                && DateTimeUtil::date(table1.CreatedDateTime) <= _contract.parmToDate()      
        {
            info("hello");
        }
what did i do wrong?

However, when i did this code in a runnable class. it returned the correct records:
  
 while select table1
            where table1.InventLocationId == 'XX'
{
        FromDate fromDate = DateTimeUtil::date(table1.CreatedDateTime);
        if(fromDate >= 25\6\2024 && fromDate <= 26\6\2024)
        {
            Info("hello");
        }
}


what i want is:

1. When fromDate and ToDate in the contract parameter are empty, then my while select should return everything
2. When ToDate is filled alone and From is Empty, then the while should return all records that have createdDateTime <= ToDate
3. When Both are filled, then it should return records in this period
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 241,315 Most Valuable Professional on at
    You can't apply DateTimeUtil::date() on the value in database. You could compare CreatedDateTime with your dates, but you need a slightly different logic anyway. I'm assuming that the contract contains dates in user's local timezone, which you can't directly compare with CreatedDateTime, because that's in UTC. You need to convert the dates before comparison.
     
    For example:
    Timezone userTimeZone = DateTimeUtil::getUserPreferredTimeZone();
utcDateTime fromUtc = DateTimeUtil::newDateTime(_contract.parmFromDate(), 0, userTimeZone);
utcDateTime toUtc = DateTimeUtil::newDateTime(_contract.parmToDate(), 0, userTimeZone);

while select table1
    where table1.InventLocationId == 'XX'
       && table1.CreatedDateTime >= fromUtc
       && table1.CreatedDateTime <= toUtc
{
    ...
}
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 241,315 Most Valuable Professional on at
    The value of toUTC variable is correct. When there is a midnight in your timezone, it's 11 PM UTC.
     
    The current query fetches records that were created on or before the 6/25/2024 11:00:00 pm, while you seem to want to include 24 hours more. The fix is trivial: take values that are smaller than the next date:
    Timezone userTimeZone = DateTimeUtil::getUserPreferredTimeZone();
utcDateTime fromUtc = DateTimeUtil::newDateTime(_contract.parmFromDate(), 0, userTimeZone);
utcDateTime toUtc = DateTimeUtil::newDateTime(_contract.parmToDate() + 1, 0, userTimeZone);

while select table1
    where table1.InventLocationId == 'XX'
       && table1.CreatedDateTime >= fromUtc
       && table1.CreatedDateTime  < toUtc
{
    ...
}
    If you want to know what the second parameter of DateTimeUtil::newDateTime() is for, look at its name:
    As you see, it's the time component. A date/time value consists of date and time; you set the date in _date parameter and time in _time parametrer.
  • Martin Dráb Profile Picture
    Martin Dráb 241,315 Most Valuable Professional on at
    Regarding "but it wasn't midnight, it was around 8 AM in UK time", you must be talking about something else. I was reacting to your remark "toUTC: 6/25/2024 11:00:00 pm  (even though i filled it with 26)".
     
    A day starts and ends at midnight, not at 8 AM. But the midnight in UK time isn't necessary the midnight in UTC timezone. If your timezone offset is UTC+1, you need to extract one our to get UTC time. That's how you add 11 PM.
     
    The timeOfDay data type represents the number of seconds since midnight. Therefore zero means the midnight.
     
    Using uctDateTime type instead of date for your parameters is possible, but it doesn't look better to me.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Finance

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans