How to cover a business week
- Management often is not interested in the date, but is much more interested in the week. The week is was allow them to plan for: such as labour, cash flows, inventory variations...
- The trick is convert the specific dates to Mondays and to Fridays.
- Microsoft SQL Server provides the datepart weekday to return numeric value of the day of the week.
- The numeric value of the day of the week also depends on the setting: which day is the 1st day of the week? Is it Sunday [the default for US English]? Or Monday? You will find out by using the @@datefirst function.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date,sales_qty,sales_price,emp_short
- FROM sales
- WHERE YEAR(sales_date) >= 2007;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2007-06-17 17:01:01.000 2.00 11.75 000010 2007-04-18 00:00:00.000 0.00 11.75 000010 2007-05-31 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-08 15:00:00.000 1.50 11.75 000010 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000030 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-11-06 10:01:01.000 9.00 11.75 000030 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 000310 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-08 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 (23 row(s) affected)
Mondays and Fridays
- SELECT sales_date,
- DATENAME(weekday, sales_date) AS 'Day of the week',
- sales_date + (2 - DATEPART(weekday, sales_date)) AS 'Monday',
- sales_date + (6 - DATEPART(weekday, sales_date)) AS 'Friday',
- sales_qty,sales_price,emp_short
- FROM sales
- WHERE YEAR(sales_date) >= 2007;
- go
sales_date Day of the week Monday Friday sales_qty sales_price emp_short ----------------------- ------------------------------ ----------------------- ----------------------- --------------------------------------- --------------------------------------- ---------- 2007-06-17 17:01:01.000 Sunday 2007-06-18 17:01:01.000 2007-06-22 17:01:01.000 2.00 11.75 000010 2007-04-18 00:00:00.000 Wednesday 2007-04-16 00:00:00.000 2007-04-20 00:00:00.000 0.00 11.75 000010 2007-05-31 00:00:00.000 Thursday 2007-05-28 00:00:00.000 2007-06-01 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 Friday 2007-07-02 00:00:00.000 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-05-03 09:00:00.000 Thursday 2007-04-30 09:00:00.000 2007-05-04 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 Saturday 2007-04-30 04:00:00.000 2007-05-04 04:00:00.000 1.00 11.75 000010 2007-05-07 11:00:00.000 Monday 2007-05-07 11:00:00.000 2007-05-11 11:00:00.000 2.00 11.75 000010 2007-05-08 15:00:00.000 Tuesday 2007-05-07 15:00:00.000 2007-05-11 15:00:00.000 1.50 11.75 000010 2007-05-07 11:30:00.000 Monday 2007-05-07 11:30:00.000 2007-05-11 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 Tuesday 2007-05-07 13:30:00.000 2007-05-11 13:30:00.000 1.00 10.00 000030 2007-05-09 14:00:00.000 Wednesday 2007-05-07 14:00:00.000 2007-05-11 14:00:00.000 1.25 10.00 000110 2007-11-06 10:01:01.000 Tuesday 2007-11-05 10:01:01.000 2007-11-09 10:01:01.000 9.00 11.75 000030 2007-05-10 14:00:00.000 Thursday 2007-05-07 14:00:00.000 2007-05-11 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 Friday 2007-05-07 14:15:00.000 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 Saturday 2007-05-07 15:00:00.000 2007-05-11 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 Sunday 2007-05-14 16:00:00.000 2007-05-18 16:00:00.000 1.00 10.00 000190 2007-05-08 05:00:00.000 Tuesday 2007-05-07 05:00:00.000 2007-05-11 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 Monday 2007-05-07 00:00:00.000 2007-05-11 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 Monday 2007-05-14 11:00:00.000 2007-05-18 11:00:00.000 3.00 10.00 000310 2007-05-11 13:00:00.000 Friday 2007-05-07 13:00:00.000 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-08 05:00:00.000 Tuesday 2007-05-07 05:00:00.000 2007-05-11 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 Tuesday 2007-05-07 07:00:00.000 2007-05-11 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 Tuesday 2007-05-07 11:00:00.000 2007-05-11 11:00:00.000 3.00 10.00 000010 (23 row(s) affected)
- This is based on the 1st day of the week.
- In my case, I use the default, which is Sunday.
- If your 1st day of the week is Monday:
- Change the calculation on line 3 from: (2 - datepart(weekday, sales_date)) to (1 - datepart(weekday, sales_date)) for the Mondays.
- Change the calculation on line 4 from: (6 - datepart(weekday, sales_date)) to (5 - datepart(weekday, sales_date)) for the Fridays.
Checking for the 1st day of the week
- SELECT @@DATEFIRST AS '1st day of the week';
- go
1st day of the week ------------------- 7 (1 row(s) affected)
The values are:
| Value | 1st day of the week |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |