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.
You can try formatDateTimeUTC() instead of formatDateTime() ,this will get UTC time into calculation.