Filtering only time on a datetime field

(0) Share

Report

Posted on by Community Member

Is it possible to filter a record with a time range on a datetime field?

I need to pick up all the lines that are inside a time range, between two dates, with only a datetime field...

Do I need to create a new field with the time section of the datetime field...!? (and update historic values with this new data)

Any suggestion?

Thank you in advance!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (0)

Report

You can filter the datetime field directly:

YOURTABLE.SETRANGE(YOURFIELD,CREATEDATETIME(Date1,0T),CREATEDATETIME(Date2,18.59T));

This sample filters between two dates (Date1 and Date2) in a certain time range.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you.

But I believe that I would get all the lines between the 00:00 of Date 1 and the 18:59 of Date 2. Is that correct?

I need all the lines between, let's say, 1700T and 1800T, of each day between 030118D and 100118D.

If want to pick the 050118 1730DT value but not the 050118 1000DT .

Was this reply helpful? Yes No
Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (1)

Report

Ok now I understand your requirement. There’s no such possibily. I think you can solve that in few steps:

1) filter your record from Date1,17.00T to Date2,18.00T

2) loop through this set of records and mark the records that has the time in your desired range

3) retrieve only the marked record (MARKEDONLY(true))

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks you.

Hope that I will not have performance issues...

I will try that way.

If it will be too slow, I think that a new time field (as key), will be much faster, right?

I need to filter several thousands of lines, 5 times (five ranges of times) in the same report...

I'm wondering (another approach) if I send all the lines between dates to the report (with no time range column), and then create there 5 tablix, with same data BUT with group filters based on the different time ranges... it will do the work faster?

Thanks for the help!

Was this reply helpful? Yes No
Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (0)

Report

yes, a Time field will be much faster for sure. This will be for sure the best way to do if you can create it in your table.

Was this reply helpful? Yes No

Community site session details

Filtering only time on a datetime field

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Featured topics

Product updates

Community site session details

Filtering only time on a datetime field

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Featured topics

Product updates