return records that got created in certain dates

(1) Share

Report

Posted on by CU04051814-0

194

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

Categories:

Integrations

Development / Customization / SDK

All responses (5)

Answers (0)

Martin Dráb 225,398 Super User on at

Like (0)

Report

return records that got created in certain dates

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.
CU04051814-0 194 on at

Like (0)

Report

return records that got created in certain dates

Hi Martin,

but it wasn't midnight, it was around 8 AM in UK time. So i don't want it to filter on any time.
if i put "from" as 25/6 and "to" as 26/6, then it should look for all createdDateTime that contain 25th and 26th dates.

regarding the time component, what does it mean if i put 0? does it mean 12 AM? i don't get it

And again, Do you think it's better to make the contract parameters as DateTime and that's it?
Suggested answer

Martin Dráb 225,398 Super User on at

Like (0)

Report
return records that got created in certain dates

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.
CU04051814-0 194 on at

Like (0)

Report

return records that got created in certain dates

Hi Martin,

it's not working properly. I have those records in the DB

i filled only ToDate with 26th of june (fromDate is empty) and it only returned the first record that was created in 25th of June. I was expecting the first three records in the screenshot to appear. (I'm intreseted in all records <= this date, regardless of time)

When i printed the values to see why this happened, here's what i got
userTimeZone: (GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London
fromUTC:
toUTC: 6/25/2024 11:00:00 pm (even though i filled it with 26)

what does this line do and what does the 0 mean? DateTimeUtil::newDateTime(_contract.parmToDate(), 0, userTimeZone);

Do you think it's better to make the contract parameters as DateTime and that's it?
Suggested answer

Martin Dráb 225,398 Super User on at

Like (0)

Report
return records that got created in certain dates

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 { ... }

return records that got created in certain dates

Helpful resources

News and Announcements

Quick Links

Community Spotlight of the Month

Blog subscriptions now enabled!

TechTalk: How Dataverse and Microsoft Fabric powers ...

more Community News

Leaderboard

Featured topics

Product updates

return records that got created in certain dates

Helpful resources

News and Announcements

Quick Links

Community Spotlight of the Month

Blog subscriptions now enabled!

TechTalk: How Dataverse and Microsoft Fabric powers ...

more Community News

Subscribe to

Leaderboard

Featured topics

Product updates