If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time:

DATEDIFF('day', start, stop) AS days

DATEPART('day', start - stop) AS days

MySQL:

DATEDIFF(stop, start) AS days

Snowflake:

DATEDIFF('day', start, stop) AS days

However, the functions above will count all the days between the two specified dates. What if you want to exclude weekend days (Saturday and Sunday)?

One way of achieving this is to create a derived table with the necessary logic. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML.

Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday; this is important, or the calculations will be one or two days off. You will also want to make sure you have the date timeframe declared in the dimension_groups that will represent your start date and end date.

The Solution

The following example is for Snowflake, and will need to be updated as necessary for your specific SQL dialect.

dimension_group: start {
  type: time
  timeframes: [raw, date, day_of_week_index] ## you can have other timeframes here too
  sql: ${TABLE}.start_date ;;
}

dimension_group: end {
  type: time
  timeframes: [raw, date, day_of_week_index] ## same here!
  sql: ${TABLE}.stop_date ;;
}

You'll need to replace ${start_date} and ${end_date} with the name of your date fields:

  dimension: weekday_datediff {
    type: number
    # This SQL is Snowflake-specific and may need to be modified for other databases.
    sql: 
        DATEDIFF('day', ${start_date}, ${end_date}) + 1 -
        DATEDIFF('week', ${start_date}, DATEADD('day', 1, ${end_date})) -
        DATEDIFF('week', ${start_date}, ${end_date})
    ;;
  }

We'll break down what each section of the SQL is doing.

DATEDIFF('day', ${start_date}, ${end_date}) + 1

This first expression naively counts days between start and end. The +1 means the ${end_date} is included as part of the count. As such, this expression will never output a day count of less than 1 (unless the start and end dates are accidentally reversed, in which case the result will be a negative number or 0).

DATEDIFF('week', ${start_date}, DATEADD('day', 1, ${end_date}))

This second expression counts Saturdays. Since there can never be fewer Saturdays than Sundays in a given range (because Sunday is after Saturday), we will check whether the last day in the range is a Saturday. This expression checks by calculating how many weeks there are in this range if the last day is forwarded by one day? For example, if the last day is a Friday, then it moves to Saturday, and that partial week doesn't count towards our total because DATEDIFF('week', ...) only counts fully completed weeks. However, if the last day is a Saturday, then it gets bumped to a Sunday, and this count will increase.

DATEDIFF('week', start_date, end_date)

This third piece counts the number of fully completed weeks, which will be the same as the number of Sundays in that range (there is one Sunday per week).

Putting all the expressions together, we subtract the Saturday count and Sunday count from the first naive count to calculate the number of weekdays elapsed in the time range.

This pattern can be adjusted to accommodate different week start days, but will need to be adjusted if your database assigns day of week index numbers differently to week days.

Articles in this section

How to Count Only Weekdays Between Two Dates

The Solution