'Midnight rule' - Fields impacted (dates show as one day later)

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

You may see issues in areas such as reports, portlets or on the database (DB) where dates show as one day later than in Clarity. This is because the 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. This article covers the fields impacted by this rule.

Resolution

Fields impacted by Table

Some examples from tables that behave in this manner are below

FIN_FINANCIALS
- All the Planned Finish Dates, the Breakeven date, are stored using the midnight rule
INV_INVESTMENTS
- Schedule Finish Date
BIZ_COM_PERIODS
- All the Fiscal Time Period End Dates
NBI_DIM_FISCAL_TIME
- Because the master table, BIZ_COM_PERIODS table uses the midnight rule, this table also uses it

Stock Fields that use the 'midnight rule'

Fiscal Time Period

Finish Date

Custom Investment Type

Budgeted Cost Finish
Planned Breakeven
Budgeted Benefit Finish
Budgeted Breakeven
Planned Benefit Finish

Projects

Finish
Baseline Finish
As Of
Planned Cost Finish
Budgeted Cost Finish
Planned Breakeven
Budgeted Benefit Finish
Budgeted Breakeven
Planned Benefit Finish

Ideas

Estimated Finish
Budgeted Cost Finish
Planned Breakeven
Budgeted Benefit Finish
Budgeted Breakeven
Finish Date
Planned Benefit Finish
Break-even Date
Planned Cost Finish

Baseline

Finish

Staff Allocations (API = teams)

(Planned Allocation) Finish
Hard Allocation Finish
Baseline Finish
Investment Finish

Tasks

Finish
Baseline Finish
Early Finish
Finish No Earlier Than
Finish No Later Than
Late Finish
Must Finish On

Assignments

Finish
Actuals Thru Date
Baseline Finish

Roadmaps

Finish

Timesheets

Time Period End Date

Resources

Date of Termination

This type of date, when used straight from the database in custom reports or workflow scripts can produce unexpected results and the Finish Date can show up as one day ahead.

Additional Information

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.