KBI 311251 Argent Reports Graphs Show No Data

Version

Argent Reports 1501-D and earlier

Date

Thursday, 9 July 2015

Summary

Argent Reports shows no data even if the Report Definition and raw data has been verified in the SQL backend

This issue only occurs when SQL Server is set to the ‘British English’ regional setting

Research shows this issue is considered a Microsoft bug, as universal date standards (that don’t change based on region or country) are not honored

Technical Background

Due to the world’s various date formats, Argent Reports passes dates into SQL in the ‘ODBC Canonical Format’, which ALL databases are supposed to follow — the format is YYYY-MM-DD

Example

The following date is supposed to mean 1 Dec 2015:

SELECT *

FROM TABLE

WHERE SNAP_TIME > ‘2015-12-01’

However, under the ‘British English’ regional setting only, the same date format means 12 Jan 2015 (e.g. YYYY-DD-MM)

This is a Microsoft bug

Resolution

Upgrade to Argent Reports 5.0A-1507-A or above to correct this Microsoft bug

To ensure we pass in ‘1 Dec 2015’ properly, the SQL query needs to be written as follows:

SELECT *

FROM TABLE

WHERE SNAP_TIME > CONVERT(datetime, ‘2015-12-01’, 120)

The above query would yield the expected results on a British English SQL server (as well as all other regions) — it successfully treats the resultant date as 1 Dec 2015

Important Note:

Notice how ‘YYYY-MM-DD’ is still being passed in initially — which means SQL understood ‘2015-12-01’ to correctly mean 1 Dec 2015 in the first place.. thus, the extra conversion is essentially redundant, but necessary for the ‘British English’ regional setting.