The InsertComputerEvidenceFromInstallerEvidenceUsage or InsertComputerEvidenceFromFileEvidenceUsage import writer steps may fail with the following error if usage date values imported from an inventory spreadsheet use an invalid format:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
Data values in the StartDate and LastUsedDate columns of installer evidence and file evidence spreadsheets used for inventory data imports must use one of the following formats (using either "/" or "-" as separator characters):
If date values are have an invalid format, the inventory import process may fail with errors like the following:
2021-10-19 00:39:25,642 [INFO ] InsertComputerEvidenceFromInstallerEvidenceUsage 2021-10-19 00:39:26,174 [INFO ] Failed to execute Writer 'InsertComputerEvidenceFromInstallerEvidenceUsage' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Usage.xml, at step line 45 Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
or
2021-10-19 00:39:25,642 [INFO ] InsertComputerEvidenceFromFileEvidenceUsage 2021-10-19 00:39:26,174 [INFO ] Failed to execute Writer 'InsertComputerEvidenceFromFileEvidenceUsage' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Usage.xml, at step line 76 Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
The following queries can be executed against an FlexNet Manager Suite On-premises compliance database to identify problematic data:
; WITH u AS ( SELECT StartDate, StartDateConverted = CASE WHEN CHARINDEX('/', StartDate) = 5 OR CHARINDEX('-', StartDate) = 5 THEN TRY_CONVERT(datetime, StartDate, 111) WHEN CHARINDEX('/', StartDate) = 3 OR CHARINDEX('-', StartDate) = 3 THEN TRY_CONVERT(datetime, StartDate, 103) END FROM dbo.ImportedInstalledInstallerEvidenceUsage WHERE CHARINDEX('/', StartDate) IN (3, 5) OR CHARINDEX('-', StartDate) IN (3, 5) ) SELECT StartDate FROM u WHERE StartDateConverted IS NULL ; WITH u AS ( SELECT LastUsedDate, LastUsedDateConverted = CASE WHEN CHARINDEX('/', LastUsedDate) = 5 OR CHARINDEX('-', LastUsedDate) = 5 THEN TRY_CONVERT(datetime, LastUsedDate, 111) WHEN CHARINDEX('/', LastUsedDate) = 3 OR CHARINDEX('-', LastUsedDate) = 3 THEN TRY_CONVERT(datetime, LastUsedDate, 103) END FROM dbo.ImportedInstalledInstallerEvidenceUsage WHERE CHARINDEX('/', LastUsedDate) IN (3, 5) OR CHARINDEX('-', LastUsedDate) IN (3, 5) ) SELECT LastUsedDate FROM u WHERE LastUsedDateConverted IS NULL
You may need to work with Flexera Support to identify problematic data that has been uploaded to Flexera One ITAM.
Prior to the fix, only dates in the format yyyy/MM/dd were allowed. With the fix, date formats as specified in this article are supported and values which do not obviously match a supported format are ignored (that is, imported as if there is no usage data specified).
Values which the import process thinks may match the required format but are not invalid dates can still result in the import process failing with an error appearing in logging as described in this article.
Also see the following related issue: Usage date format described in inventory spreadsheet schema documentation doesn't match format accepted by import code (IOJ-2227030)
This issue has been fixed in the following FlexNet Manager Suite releases: 2020 R1.1 / Sep 2020 (Cloud), 2020 R2 (On Premises)
Affected components: Inventory import (read/write/export), Usage
Master issue ID: IOJ-2122692
Also known as: FNML-70963
Feb 02, 2022 01:27 PM - edited Jun 12, 2022 03:49 AM