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.