Determining the First Date in a Week

Here's a simple algorithm to determine the first date in the week a certain date falls in. This is handy for reporting on aggregate values by week, displayed as "Week of SomeDate". The gotcha is that you need to know what day the user thinks of as the first day of a week. Some people consider Sunday to be the first day, others Monday, and so on. The DOW() function can accept a value indicating what day is the first one, from 0 for Sunday to 7 for Saturday. You'll need to ask the user which day they want a week to start from.

In this expression, ldDate is the date from which to determine the first date in the week (for example, a transaction date) and lnFirstDay is the day number for the first day in a week.

ldDate - dow(ldDate, lnFirstDay) + 1

Here's an example that shows the total freight charged by week, with Sunday being the first day of the week.

select OrderDate - dow(OrderDate, 0) + 1 as Week, sum(Freight) from Orders group by 1