Home » Categories » SQCpack » SQCpack 7

SP: Issues associated with Time-only Identifier column in a data group

Versions of SQCpack 7 prior to 7.0.15228.1 incorrectly stored a Time-only Identifier in a datetime2 SQL Server column. The datetime2 data type stores both date and time information. When the time-only value was stored in the datetime2 column SQL Server arbitrarily chose a date to use (either a date in 1899 for data imported from SQCpack 6.5, or the current date).

This "extra" date portion caused problems when filtering on a Time-only Identifier column - there was no error message - but the rows passing through the filter were incorrectly filtered in most cases.

For versions of SQCpack 7.0.15228.1 and higher:

  • When the database is opened a one-time migration is performed that converts all Time-only identifier columns to the correct SQL Server data type time(7).

  • These versions of SQCpack in Data Entry are able to correctly handle a Time-only identifier of type datetime2. This is necessary because someone might use an older version of SQCpack to create or import a data group thus resulting in a Time-only identifier of type datetime2.

  • SQCpack correctly handles conversions between any of these types of identifiers: Date, DateTime, Time, and Text.

  • Filtering on a Time-only identifier works correctly.

For versions of SQCpack prior to 7.0.15228.1:

  • Filtering on a Time-only identifier results in no error message but the rows passing through the filter are incorrectly filtered in most cases.

  • When using an SQCpack database that has had the one-time migration performed by SQCpack version 7.0.15228.1 and higher, in Data Entry when dealing with a Time-only identifier you may get the error "Unexpected column type encountered”. Or, the time value entered is not correctly saved to the database. To correct this error, follow the instructions in the next bullet point.

  • Follow the instructions under "To fix an incorrect Time-only identifier column” to correct the situation where someone used an older version of SQCpack to create or import a data group in an SQCpack database that already had the one-time migration associated with this issue performed - thus resulting in a Time-only identifier of type datetime2 rather than time(7).

To fix an incorrect Time-only identifier column

SQCpack 7.0.15228.1 and higher performs a one-time migration to fix any incorrect Time-only identifier column types. After that, if someone uses a version of SQCpack prior to 7.0.15228.1 to create or import a data group this results in a Time-only identifier of type datetime2 rather than time(7). Since the one-time database migration has already been performed (and will not be reapplied) it is necessary to manually correct these Time-only identifiers as follows.

  1. Ensure that you are running SQCpack 7.0.15228.1 or higher.

  2. Edit the data group and change the identifier type from Time to DateTime. Save your changes.

  3. Edit the data group and change the identifier type from DateTime to Time. Save your changes. The SQL Server data type of the Time-only identifier column is now correctly set to time(7).

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
There are no related articles for this article.