Increase precision of type datetime for SQL Server

• Type: Story
• Status: Closed
• Priority: Critical
• Resolution: Completed
• Affects Version/s: Master
• Fix Version/s: 7.2.X, Master
• Component/s: Environments > Databases
• Labels:
  • ee-ts
• Branch Version/s:
  7.2.x
• Backported to Branch:
  Committed
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal-ee/pull/21624,https://github.com/brianchandotcom/liferay-portal/pull/62935

In SQL Server the type DATETIME has smaller precision that a millisecond. In particular, time values will be rounded to one of the following values: .xx0 s, .xx3 s, or .xx7 s. 

Starting with version 2008, the new type DATETIME2 was introduced which can allow up to a precision of 100 ns. The precision can be customized using the type DATETIME2(p), where p can range from 0 to 7, with a default value of 7. 

Using DATETIME can lead to problems when comparing dates. See LPS-84506 for an example.