Reconciliation Error : Failed to import inventory devices with error message 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

[AjaykumarPatil]

Hi, Getting below error. Attach logs.

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.
Found 0 computer evidence records from installer evidence usage

[durgeshsingh]

This is SQL error & would suggest to log a case with flexera Support. They will check your configuration & provide you some script to solve.

[ChrisG]

On the surface, this looks to be the same error that is covered by the issue IOJ-2122692 included on the following list: FlexNet Manager Suite 2020 R2 Resolved Issues. This error occurs if a date value in usage data that has been imported from an inventory source has an invalid format.

If I'm following right, I think you are using the 2020 R2 release (or later), so maybe you are encountering a slightly different type of data that isn't covered by the fix for IOJ-2122692.

Can you try running the following queries against your compliance database? Neither query should return any results, but if they do return data then it may show some date values with an invalid format that will need to be corrected.

; 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 TOP 10 *
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 TOP 10 *
FROM u
WHERE LastUsedDateConverted IS NULL

 Also, what inventory sources are you importing usage data from?

[AjaykumarPatil]

@ChrisG  Thanks,  It did return with the date 08/26/2021. We tried some manual imports for one device on that date.

Will you please guide me to correct that date?

[ChrisG · Accepted solution]

That makes sense - using a date value formatted like that is likely to cause this error.

The values in the StartDate and LastUsedDate columns of installer evidence and file evidence spreadsheets used to manually import inventory should be text values in the following format: yyyy/MM/dd. For example, the date 26 August 2021 should be represented as: "2021/08/26".

(You could also use the format dd/MM/yyyy, but putting the 4-digit year first is less ambiguous so is what I would recommend.)

[AjaykumarPatil]

@ChrisG  Thanks a lot. It's working as expected now. 

[ChrisG]

For reference, see the following article which notes some incorrect information in documentation related to the problem discussed in this thread: Known Issue: Usage date format described in inventory spreadsheet schema documentation doesn't match format accepted by import code (IOJ-2227030)