Datetime values are inserted into the SQL Server incorrectly

Diagnostics

Product: Reporting Server

Release: 8205

Primary OS: Windows

Solution Date: 2020-12-08

Problem

Datetime values are inserted into the SQL Server incorrectly.

The values inserted are:

2019-12-04 00:00:00.000
2019-12-05 00:00:00.000

But they should be:

20191204 00:00:00:01
20191204 23:59:59:999

Solution

The format of the field in the synonym's Master File affects the values because the underlying SQL's CAST values differ.

To address this issue, change the format in the Master File as follows:

From: USAGE=HYYMDs, ACTUAL=HYYMDs

To: USAGE=YYMD, ACTUAL=DATE

Information is published on an "as is" basis without a warranty of any kind. Information Builders, Inc. ("IBI") hereby represents that such information has not been subject to any formal testing or review by IBI. IBI assumes no responsibility for usage of such information, including the implementation of solutions associated therewith. The customer assumes responsibility for the results obtained from such information. IBI is not responsible for and shall not be held liable for the results obtained, including but not limited to any errors, delays or omissions. IBI disclaims any liability for the completeness or accuracy of such information.