search cancel

Finish date of Idea is showing in INV_INVESTMENTS as one day later


Article ID: 117890


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


The Finish Date of Clarity ideas show in the SCHEDULE_FINISH field in the INV_INVESTMENTS table as one day later than what is set in Clarity.

This issue is also seen in portlets, where the finish date of ideas will show as rolling to the next day.

Steps to Reproduce:

  1. Create a new idea in Clarity
  2. Set the Start Date of the idea as 01/03/2018 and the Finish Date as 05/17/2018
  3. Run the below query and make a note of the schedule_finish date 

select id, name, schedule_start, schedule_finish from inv_investments where id = < > 

Expected Results: The schedule finish date should be the same in UI and database (05/17/2018 in the above example)
Actual Results: Schedule finish date always show as 1 day advance in the database (shows as 05/18/2018 in the DB)

Example for a portlet:

Code fragment used in the portlet:

  • TO_CHAR(inv.schedule_start, 'MM/DD/YYYY') schedule_start,    
  • TO_CHAR(inv.schedule_finish, 'MM/DD/YYYY') schedule_finish,   

Results: Finish date for an idea of 6/30/2019 changes to 7/1/2019 on the portlet.


Release: All Releases
Component: Clarity Project Management


This was reviewed by  Sustaining Engineering team as CLRT-59397 and determined to be working as expected. The date stored in the database is +1 day because Idea's Finish dates default to a "midnight" date. However, in the case of projects, the date also has a time component.

For example, a project's finish is usually 5pm, so the date in the database would match what you see in the UI. In this case, the date doesn't represent 'midnight' and therefore isn't +1 day.

See Fields impacted by 'midnight rule' (dates show as one day later for details on fields impacted. 


Option to workaround behavior: Change the end time of the Idea Finish Date field to be earlier than 12:00 am, then the Clarity UI and DB will match. 

  1. In Clarity go to Home->Ideas
  2. Click on Options then Configure
  3. Move Finish Date to ‘Selected Columns’ if not there already
  4. Click Save
  5. From the 'List Column Section' drop down, select ‘Fields’
  6. Click the properties icon next to ‘Finish Date’
  7. In the ‘Date/Time Format Scale’ drop down, select ‘Day Time’
  8. Click ‘Save and Return’ then click ‘Return’
  9. In the Finish Date field on the Idea list, change the Time of the Idea from 12:00 AM to an earlier end time such as 5:00 PM, then click Save.

Additional Information