How to Display 'Weekday/Weekend' and 'Day/Night' in Bold BI with SQL Server Date Field Expressions
When working with date fields in expressions, you may need to display “Weekday”/“Weekend” and “Day”/“Night”. With this article, you will learn how to display “Weekday”/“Weekend” and “Day”/“Night” in BoldBI using the SQL server date field expressions.
Displaying “Weekday”/“Weekend” using SQL server Date Field Expressions
You can use the following expression to determine if a date field falls on a weekday or a weekend:
[dt] stands for ‘DateTime’ field,
if(DATEPART(Weekday,[dt]) != 1 AND DATEPART(Weekday,[dt]) < 7 , 'weekday','weekend')
Expression Designer Screenshot:
Explanation:
This expression uses the Datepart function to get the day of the week for the given date field, where Monday is represented by 2 and Sunday by 1. If the day of the week is between 2 and 6 (inclusive), it indicates a weekday, and the expression returns “Weekday”, otherwise it returns “Weekend”.
Displaying “Day”/“Night” using Date Field Expressions
You can use the following expression to determine if a date field falls within the time range of a Day or a Night:
[dt] stands for ‘DateTime’ field,
IF(DATEPART(hour, Convert(datetime, [dt] ,103)) > 8 AND DATEPART(hour, Convert(datetime, [dt] ,103)) < 17 , 'Day','Night')
Expression Designer Screenshot:
Explanation:
This expression uses the Datepart function to extract the time value from the date field and then compares it with the time values of 8:00 AM and 5:00 PM. If the time is greater than 8:00 AM and less than 5:00 PM, it indicates daytime, and the expression returns “Day”, otherwise it returns “Night”.
Expected Result:
Related Links:
