Fix Issue : SQL type ‘datetime’ is not registered, register the type with method RegisterDataTypes(…)

Recently I have upgraded one of the projects from Kentico 8.2.XX to Kentico 12.

After the upgrade, I think there is no issue since the project using Kentico features without customization until I tried to add a new column in Biz Form and trying to save the new field but the error occur.

Error Message

Message: SQL type ‘datetime’ is not registered, register the type with method RegisterDataTypes(…)

Exception type: System.NotSupportedException
Stack trace:
at CMS.DataEngine.DataTypeManager.GetFieldType(String sqlType, Int32 size, Boolean throwException)
at CMS.DataEngine.FieldBase`1.LoadFromTableData(DataRow row, Boolean isPrimary, Boolean isSystem)
at CMS.FormEngine.FormFieldInfo.LoadFromTableData(DataRow row, Boolean isPrimary, Boolean isSystem)
at CMS.DataEngine.DataDefinition.LoadFromDataStructure(String tableName, TableManager manager, Boolean isSystem)
at CMS.DataEngine.TableManager.GetDatabaseFields(String tableName)
at CMS.DataEngine.TableManager.GetOldFields(String tableName, String definition, Boolean loadOldDefinition)
at CMS.DataEngine.TableManager.UpdateTableByDefinition(UpdateTableParameters parameters)
at CMS.DataEngine.DataClassInfoProvider.EnsureDatabaseStructure(DataClassInfo classInfo, Boolean updateSystemFields)
at CMS.DataEngine.DataClassInfoProvider.SetInfo(DataClassInfo info)
at CMSModules_AdminControls_Controls_Class_FieldEditor_FieldEditor.UpdateDependencies(DataClassInfo dci, FormFieldInfo updatedFieldInfo, Boolean& updateInheritedForms)

The solution to fix the issue.

*Remember to back the db up before doing something like this.

Open Microsoft SQL Management Studio (SSMS) and use the project db.

Run SQL code below code to fix the issue.

ALTER TABLE [<Table_Name>]
DROP
CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormInserted],
CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormUpdated]

ALTER TABLE [<Table_Name>]
ALTER column FormInserted datetime2

ALTER TABLE [<Table_Name>]
ALTER column FormUpdated datetime2

ALTER TABLE [<Table_Name>]
ADD
CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormInserted] DEFAULT (‘1/1/0001 12:00:00 AM’) FOR [FormInserted],
CONSTRAINT [DEFAULT_Form_<FormCodeName>_FormUpdated] DEFAULT (‘1/1/0001 12:00:00 AM’) FOR [FormUpdated]

Explanation

The issue happens because the data type in SQL for column FormInserted and FormUpdated still using the old data type for type DateTime. So the SQL code above will remove constrain for the table and alter column FormInserted and FormUpdated to use Type datetime2 instead of datetime.
After successful, the constrain added back to the table.