How to Filter Weekdays and Weekends from Date field

In Bold BI, we can make use of the expressions to split the date fields. The function “DATENAME()” is used to retrieve a specific part of a given date as a string.

Syntax:

DATENAME (date_part,date_expression)

Example:

DATENAME ("day", [date_field])

To determine whether a date falls on a weekday or a weekend, the following conditions can be used:

Weekdays:

If the day of the week extracted from the “exam_date” is either Sunday or Saturday, the result is NULL. Otherwise, the “exam_date” is cast as a date.

PostgreSQL / MySQL:

IF(TRIM(DATENAME(day, [exam_date])) = 'Sunday' OR TRIM(DATENAME(day, [exam_date])) = 'Saturday', NULL, CAST([exam_date] AS DATE))

MSSQL:

    IF(DATENAME(WEEKDAY, exam_date) = 'Saturday' OR DATENAME(WEEKDAY, exam_date) = 'Sunday',NULL,CAST(exam_date AS DATE))      

Weekends:

If the day of the week extracted from the “exam_date” is either Sunday or Saturday, the “exam_date” is cast as a date. Otherwise, the result is NULL.

PostgreSQL / MySQL:

IF(TRIM(DATENAME(day, [exam_date])) = 'Sunday' OR TRIM(DATENAME(day, [exam_date])) = 'Saturday', CAST([exam_date] AS DATE), NULL)

MSSQL:

IF(DATENAME(WEEKDAY, exam_date) = 'Saturday' OR DATENAME(WEEKDAY, exam_date) = 'Sunday', CAST(exam_date AS DATE),NULL)

Result:

Save these above expressions and bind them to a widget to see the calculated dates.

Additional References

• Configure Date Expression