Finish date is showing one day later than in Clarity

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

On your report, the Finish Date is showing one day later than in the Clarity GUI. This difference is also observed when comparing dates in the DB and the Classic UI.

Environment

Release: All Supported Clarity releases

Cause

This date is stored in the database with a midnight time stamp, having 00:00 as the hour. Using a database querying tool, a midnight timestamp is treated as the start of the date, but in Clarity, it is interpreted as the day before if this timestamp is used for a Finish Date. This is referred to as a 'midnight rule' for storing dates in the database tables.

Many of Clarity's dates have timestamps associated with them (for example, the task ends at 5 pm) and many do not. If a date does not have a times stamp associated with it, it is pushed to the very first instance of the next day (midnight) so that any date comparison logic will be fully effective (for example: provide all tasks before 3/7 will get tasks of any timestamp on 3/7). Everything within the application interprets the date stored in the database. If a Finish Date is the first value of a day (midnight) that really means it's the day before.

Some dates have timestamps that cause them to be stored in the DB as 'the next day at midnight; when in reality, they are entered and displayed in the UI as the previous day. The application has code logic that interprets these dates and always shows them as the correct date in the UI.

For a list of fields impacted, see: 'Midnight rule' - Fields impacted (dates show as one day later)

Resolution

We encourage you to handle the midnight time stamp on the Finish Date with the help of a function defined within the Clarity database. Examples of an out-of-the-box database function available:

MSSQL: CAL_TRUNC_DATE_FCT or cop_calc_finish_fct or cop_calc_finish_time or COP_DATE_TRUNC_FCT
ORACLE: COP_CALC_FINISH_FCT or COP_CALC_FINISH_TIME_FCT or COP_DATE_TRUNC_FCT

NOTE: If you use a database function within a Clarity NSQL Query be sure to use the prefix @DBUSER@. If you do not use this prefix, you may experience unpredictable data results or in some cases cause a message indicating the function cannot be found or a security error message to be generated when running the query.

Example: @SELECT:DIM_PROP:USER_DEF:IMPLIED:task:@[email protected]_calc_finish_time_fct(t.prfinish):finish_date@

Alternatively, you can create a similar function to set the finish date to (date_of_termination - 0.0001), this effectively rolls it back 1/10000th of a second to get it to the correct date.

If there is just one date that you would like to change, go to that date in the application and toggle it (change it to another date and then change it back). That will reset the time stamp on it.