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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to use less than i...
Finance | Project Operations, Human Resources, ...
Suggested answer

How to use less than in date range

(0) ShareShare
ReportReport
Posted on by junior AX 1,552

Hi,

I want to put a query range for date in my report

I have fromDate(EDT FromDate) and ToDate (EDT ToDate) in my contract class

FromDate is filled with 5/9/2020

ToDate is filled with 5/9/2021

I want the range to be the following:

CreatedDateTime >= FromDate AND

CreatedDateTime < ToDate+1

I tried using

qbds. AdsRange(fieldNum(PurchTable, CreatedDateTime)).value(queryRange(FromDate, ToDate+1))

But i'm getting three issues in my query result 

1. It's not adding +1 to the ToDate

2. I want less than ToDate and not less than or equal ToDate. Also the input date is appearing as 2020-09-04T23:00:00 in the query instead of 5/9/2020

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 236,353 Most Valuable Professional on at
    RE: How to use less than in date range

    I'm pretty sure that the plus operator works. I think you're confused by the same thing as in your last point - that the database with UTC values. If your timezone is UTC+1, your local time 2020-09-02T00:00:00 is 2020-09-04T23:00:00 in UTC.

    If you want less than toDate instead of "less than or equal" toDate, simply subtract 1 from toDate.

  • junior AX Profile Picture
    junior AX 1,552 on at
    RE: How to use less than in date range

    Hi Martin,

    FromDate = 5/9/2020

    ToDate = 5/9/2021

    If i use the date2StrXpp function i'm getting the result i want which is greater than or euqal to from date and less than ToDate 1

    as you can see below

    qbdsPurchLine.addRange(fieldnum(PurchLine,createdDateTime)).value('(createdDateTime >= ' date2StrXpp(purchLinesContract.parmCreatedDateFrom()) ') && (createdDateTime < ' date2StrXpp(purchLinesContract.parmCreatedDateTo() 1) ')');


SELECT VendAccount, ItemId, createdDateTime, PurchId, PurchStatus
 FROM PurchLine(PurchLine) WHERE
  (((createdDateTime >= 05\09\2020) && (createdDateTime < 06\09\2021)))
 JOIN InventLocationId, PurchName
 FROM PurchTable(PurchTable) ON PurchLine.PurchId = PurchTable.PurchId}

    So are you saying if use the below instead where my VM time zone (UTC) coordinated universal time, then it's the same result?

    qbdsPurchLine.addRange(fieldnum(PurchLine,CreatedDateTime)).value(queryRange(purchLinesContract.parmCreatedDateFrom(), purchLinesContract.parmCreatedDateTo()-1));


SELECT VendAccount, ItemId, createdDateTime, PurchId, PurchStatus
FROM PurchLine(PurchLine) 
 ((createdDateTime>='2020-09-04T23:00:00' AND createdDateTime<='2021-09-03T23:00:00'))
 JOIN InventLocationId, PurchName FROM PurchTable(PurchTable) ON PurchLine.PurchId = PurchTable.PurchId

    I mean is ((createdDateTime>='2020-09-04T23:00:00' AND createdDateTime<='2021-09-03T23:00:00'))

    the same as

    (((createdDateTime >= 05\09\2020) && (createdDateTime < 06\09\2021)))

    i don't think using minus 1 helped here

  • Martin Dráb Profile Picture
    Martin Dráb 236,353 Most Valuable Professional on at
    RE: How to use less than in date range

    I'm saying that 5/9/2020 in your timezone doesn't have to be 5/9/2020 in UTC. If you don't convert your local date to UTC, you're comparing apples and oranges.

    No, I'm not saying at all that your different queries will provide the same result.

    Also, please don't use the extended query range syntax unless absolutely necessary. It's error-prone and difficult to debug, and it's definitely not needed in the simple case like this.

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,824 Moderator on at
    RE: How to use less than in date range

    You are using Date while generating the range, but since the field is DateTime, ax converts it to Date + 00:00:00(time). It takes 1 hour because of the timezone. Subtracting one from 00 gets 23 and decreases the day by 1 (04)

    It's best to use DateTime so you don't mess with the details. Below is a similar thread discussed today, you might want to read it.

    community.dynamics.com/.../utcdatetime-range-does-not-work

  • junior AX Profile Picture
    junior AX 1,552 on at
    RE: How to use less than in date range

    Hi Martin,

    how to convert my time zone to utc, when i checked my VM it was UTC.

    can you please explain more what should i do in my query to get the desired result without using date2strXpp?

  • Martin Dráb Profile Picture
    Martin Dráb 236,353 Most Valuable Professional on at
    RE: How to use less than in date range

    You can see an example if you follow the link provided by Ergün just above your question.

    Here is the most important part:

    utcDateTime startDateUtc = DateTimeUtil::removeTimeZoneOffset(filterScheduledStart.dateTimeValue(), DateTimeUtil::getUserPreferredTimeZone());

    You need to know which timezone offset you want to remove. The user's timezone (obtained by DateTimeUtil::getUserPreferredTimeZone()) is typically what you want.

  • junior AX Profile Picture
    junior AX 1,552 on at
    RE: How to use less than in date range

    Hi Martin and Ergun,

    Thanks alot both. but my contract parameters are of type date not dateTime

    So i didn't get it how can i remove the time zone in the sentence below if there is no time?

    qbdsPurchLine.addRange(fieldnum(PurchLine,CreatedDateTime)).value(queryRange(purchLinesContract.parmCreatedDateFrom(), purchLinesContract.parmCreatedDateTo()-1));

  • ergun sahin Profile Picture
    ergun sahin 8,824 Moderator on at
    RE: How to use less than in date range

    docs.microsoft.com/.../dynamics.ax.application.datetimeutil.newdatetime

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,028

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 878 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 579 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans