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

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

book

Article ID: 227147

calendar_today

Updated On:

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.

See also: