How to Handle Date Differences in MSSQL and MySQL Datasources

When working with different datasources such as MSSQL and MySQL, it’s important to understand that the same functions may not work identically across these platforms. This is particularly true when dealing with date differences.

The Issue

When using the expression DATEDIFF(Month, [Date],TODAY()) to filter records by month, MSSQL datasource filters the records correctly. However, when the same expression is applied to a MySQL datasource, it returns additional records from the previous month.

The Reason

In MySQL, there is no direct function for DATEDIFF. Instead, the TIMESTAMPDIFF function is used internally. This function provides a difference taking the date into account. If your usage date is within 30 days of the next or previous month, then those data are also considered.

The Solution

To get the exact month difference in MySQL, use the PERIOD_DIFF function with DATE_FORMAT as shown in the expression below:

PERIOD_DIFF(DATE_FORMAT([date], "%Y%m"), DATE_FORMAT(CAST(CURDATE() AS DATE), "%Y%m"))

This expression can be used in a shared filter in the dashboard, which should work for both MSSQL and MySQL databases.

Steps to Implement

1. Create the expression in the MySQL datasource:

PERIOD_DIFF(DATE_FORMAT(CAST(CURDATE() AS DATE), "%Y%m"),DATE_FORMAT([date], "%Y%m"))

2. Assign the expression in the widget.

3. Preview and filter the widget.
   

   

By following these steps, you should be able to filter records by month accurately in both MSSQL and MySQL datasources.

References

• MySQL Date Functions
• MSSQL Date Functions