Known Issue: InsertComputerEvidenceFrom*EvidenceUsage import writer steps may fail with error due to usage date values in invalid format: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" (IOJ-2122692)

[#1]

Synopsis

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 

Details

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):

• yyyy/MM/dd (example: "2021/12/15")
• dd/MM/yyyy (example: "15/12/2021")

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.

Fix details

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.

Additional information

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)

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2020 R1.1 / Sep 2020 (Cloud), 2020 R2 (On Premises)

Other information

Affected components: Inventory import (read/write/export), Usage

Master issue ID: IOJ-2122692

Also known as: FNML-70963