How to handle date format mismatch in dashboard parameters in SQL Server?

Published:

There are multiple date formats supported in dashboard parameters which are the following

DD/MM/YYYY, MM/DD/YYYY and YYYY/MM/DD

When working with dashboard parameters in Bold BI, you may encounter the date format mismatches. This article helps you troubleshoot the problem.

Issue Details:

In Microsoft SQL Server, when the date format is mismatched, the server will throw the below error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Cause:

This error occurs when there is a mismatch between the date format used in the dashboard parameter and the date format used in the SQL Server.

Solution:

To overcome this problem, we have the following solutions:

Solution 1:

Change the dashboard parameter format to match the SQL Server date format. Common formats include MM/DD/YYYY (if the SQL Server has MDY dateformat) or YYYY/MM/DD.

Solution 2:

Ensure that the SQL Server database date format is the same as the system date format and the parameter date format. If necessary, you can modify the SQL Server date format using the appropriate commands.

To check current date format

To set the date format in database

By ensuring that the date formats are consistent across your dashboard parameters and SQL Server, you can avoid errors related to data type conversion and ensure the smooth operation of your Bold BI dashboards.