In OQL Query i want to fetch records which has From Date To Date between selected Date

  OQL Query as below: Variable: $SelectedDate = trimToDaysUTC([%CurrentDateTime%])   'SELECT  distinct Employee/FullName as ResourceName , epa/AllocationStatus  ,epa/AllocationInPer as AllocationPercentage ,Employee/Emp_ID as ResourceId FROM CM.Employee_Project_association as epa LEFT JOIN epa/CM.Employee_Project_association_Employee/CM.Employee as Employee WHERE (epa/FromDate <= ''' + formatDateTime($SelectedDate,'yyyy-MM-dd')  + ''' AND epa/ToDate >= ''' + formatDateTime($SelectedDate,'yyyy-MM-dd')  + ''')'   When we select Date “08-29-2022” on page and click on “Apply” button, OQL query always convert  formatDateTime($SelectedDate,'yyyy-MM-dd')  to  “2022-08-28”  So my output coming wrong. I checked in variables on debug. It always take session value from variable: => Selected Date     Datetime   UTC Time: 2022-08-29 00:00:00.000 Session Time: 2022-08-28 00:00:00.000 -0400   It always take session value.   Please help me if you know any solution.    
1 answers

Hi Roshan,

You can try formatDateTimeUTC() instead of formatDateTime() ,this will get UTC time into calculation.



Hope this helps!