How to get the weekdays date range with MSSQL
I often need to get the Mondays and Fridays for a date. It's actually a fairly easy gymnastic, convert to the week and get the first day of the week or the 4th day of the week.
This operation relies on the Microsoft SQL Server ability to change the first day of the week. The first day of the week can be either a Sunday, a Monday or any other day.
- SET datefirst 1
This way, I have set Monday to be the first day of the week. A Friday would be 5, and a Sunday would be 7 [the default for US/English].
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
- FROM sales;
- go
sales_date sales_qty sales_price ----------------------- --------------------------------------- --------------------------------------- 2005-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2005-11-06 10:01:01.000 0.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2005-12-06 11:01:01.000 8.00 11.75 2006-02-06 13:01:01.000 0.00 NULL 2006-03-06 14:01:01.000 5.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-04-06 15:01:01.000 4.00 11.75 2006-05-06 16:01:01.000 3.00 11.75 2004-10-06 10:01:01.000 0.00 NULL 2005-11-06 10:01:01.000 9.00 11.75 2006-06-06 17:01:01.000 0.00 11.75 2006-01-06 12:01:01.000 7.00 11.75 2006-02-06 13:01:01.000 6.00 11.75 2005-03-06 14:01:01.000 5.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 3.00 11.75 2007-06-06 17:01:01.000 2.00 11.75 2007-04-06 00:00:00.000 0.00 11.75 2007-05-01 00:00:00.000 5.00 11.75 2007-07-06 00:00:00.000 1.00 11.75 2007-05-03 09:00:00.000 3.00 11.75 2007-05-05 04:00:00.000 1.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2004-03-06 14:01:01.000 0.00 NULL 2005-04-06 15:01:01.000 4.00 11.75 2005-05-06 16:01:01.000 3.00 11.75 2006-06-06 17:01:01.000 2.00 11.75 2006-07-06 19:01:01.000 1.00 11.75 2006-03-06 14:01:01.000 5.00 11.75 2006-04-06 15:01:01.000 4.00 11.75 2007-05-07 11:00:00.000 2.00 11.75 2006-06-06 17:01:01.000 0.00 NULL 2006-07-06 19:01:01.000 1.00 11.75 2005-10-06 10:01:01.000 10.00 11.75 2007-05-08 15:00:00.000 1.50 11.75 2005-12-06 11:01:01.000 8.00 11.75 2006-01-06 12:01:01.000 7.00 11.75 2007-05-07 11:30:00.000 2.00 10.50 2007-05-08 13:30:00.000 1.00 10.00 2006-07-06 19:01:01.000 1.00 11.75 2007-05-09 14:00:00.000 1.25 10.00 2007-11-06 10:01:01.000 9.00 11.75 2007-05-10 14:00:00.000 5.00 10.00 2007-05-11 14:15:00.000 0.00 10.00 2007-05-12 15:00:00.000 0.00 10.00 2007-05-13 16:00:00.000 1.00 10.00 2007-05-08 05:00:00.000 1.00 10.00 2007-05-07 00:00:00.000 2.00 10.00 2007-05-14 11:00:00.000 3.00 10.00 2006-07-06 00:00:00.000 1.00 11.75 2007-05-11 13:00:00.000 3.50 10.00 2007-05-08 05:00:00.000 0.00 10.00 2007-05-08 07:00:00.000 2.00 10.00 2007-05-08 11:00:00.000 3.00 10.00 (57 row(s) affected)
Getting the Mondays and Fridays
As mentioned earlier, this is only a little bit of gymnastic with the dateadd and datediff.
- SELECT sales_date,
- DATENAME(dw, sales_date) AS 'Day of sale',
- DATEADD(wk, DATEDIFF(wk,0,sales_date), 0) AS 'Monday of sale',
- DATENAME(dw,DATEADD(wk, DATEDIFF(wk,0,sales_date), 0)) AS 'Day of the week',
- DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,sales_date), 0)) AS 'Friday of sale',
- DATENAME(weekday,DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,sales_date), 0))) AS 'Day of the week',
- sales_qty,
- sales_price
- FROM sales;
- go
sales_date Monday of sale Day of the week Friday of sale Day of the week sales_qty sales_price ----------------------- ----------------------- ------------------------------ ----------------------- ------------------------------ --------------------------------------- --------------------------------------- 2005-10-06 10:01:01.000 2005-10-03 00:00:00.000 Monday 2005-10-07 00:00:00.000 Friday 10.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2005-11-06 10:01:01.000 2005-11-07 00:00:00.000 Monday 2005-11-11 00:00:00.000 Friday 0.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2005-12-06 11:01:01.000 2005-12-05 00:00:00.000 Monday 2005-12-09 00:00:00.000 Friday 8.00 11.75 2006-02-06 13:01:01.000 2006-02-06 00:00:00.000 Monday 2006-02-10 00:00:00.000 Friday 0.00 NULL 2006-03-06 14:01:01.000 2006-03-06 00:00:00.000 Monday 2006-03-10 00:00:00.000 Friday 5.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2006-04-06 15:01:01.000 2006-04-03 00:00:00.000 Monday 2006-04-07 00:00:00.000 Friday 4.00 11.75 2006-05-06 16:01:01.000 2006-05-01 00:00:00.000 Monday 2006-05-05 00:00:00.000 Friday 3.00 11.75 2004-10-06 10:01:01.000 2004-10-04 00:00:00.000 Monday 2004-10-08 00:00:00.000 Friday 0.00 NULL 2005-11-06 10:01:01.000 2005-11-07 00:00:00.000 Monday 2005-11-11 00:00:00.000 Friday 9.00 11.75 2006-06-06 17:01:01.000 2006-06-05 00:00:00.000 Monday 2006-06-09 00:00:00.000 Friday 0.00 11.75 2006-01-06 12:01:01.000 2006-01-02 00:00:00.000 Monday 2006-01-06 00:00:00.000 Friday 7.00 11.75 2006-02-06 13:01:01.000 2006-02-06 00:00:00.000 Monday 2006-02-10 00:00:00.000 Friday 6.00 11.75 2005-03-06 14:01:01.000 2005-03-07 00:00:00.000 Monday 2005-03-11 00:00:00.000 Friday 5.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 3.00 11.75 2007-06-06 17:01:01.000 2007-06-04 00:00:00.000 Monday 2007-06-08 00:00:00.000 Friday 2.00 11.75 2007-04-06 00:00:00.000 2007-04-02 00:00:00.000 Monday 2007-04-06 00:00:00.000 Friday 0.00 11.75 2007-05-01 00:00:00.000 2007-04-30 00:00:00.000 Monday 2007-05-04 00:00:00.000 Friday 5.00 11.75 2007-07-06 00:00:00.000 2007-07-02 00:00:00.000 Monday 2007-07-06 00:00:00.000 Friday 1.00 11.75 2007-05-03 09:00:00.000 2007-04-30 00:00:00.000 Monday 2007-05-04 00:00:00.000 Friday 3.00 11.75 2007-05-05 04:00:00.000 2007-04-30 00:00:00.000 Monday 2007-05-04 00:00:00.000 Friday 1.00 11.75 2006-10-06 10:01:01.000 2006-10-02 00:00:00.000 Monday 2006-10-06 00:00:00.000 Friday 10.00 11.75 2004-03-06 14:01:01.000 2004-03-01 00:00:00.000 Monday 2004-03-05 00:00:00.000 Friday 0.00 NULL 2005-04-06 15:01:01.000 2005-04-04 00:00:00.000 Monday 2005-04-08 00:00:00.000 Friday 4.00 11.75 2005-05-06 16:01:01.000 2005-05-02 00:00:00.000 Monday 2005-05-06 00:00:00.000 Friday 3.00 11.75 2006-06-06 17:01:01.000 2006-06-05 00:00:00.000 Monday 2006-06-09 00:00:00.000 Friday 2.00 11.75 2006-07-06 19:01:01.000 2006-07-03 00:00:00.000 Monday 2006-07-07 00:00:00.000 Friday 1.00 11.75 2006-03-06 14:01:01.000 2006-03-06 00:00:00.000 Monday 2006-03-10 00:00:00.000 Friday 5.00 11.75 2006-04-06 15:01:01.000 2006-04-03 00:00:00.000 Monday 2006-04-07 00:00:00.000 Friday 4.00 11.75 2007-05-07 11:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 2.00 11.75 2006-06-06 17:01:01.000 2006-06-05 00:00:00.000 Monday 2006-06-09 00:00:00.000 Friday 0.00 NULL 2006-07-06 19:01:01.000 2006-07-03 00:00:00.000 Monday 2006-07-07 00:00:00.000 Friday 1.00 11.75 2005-10-06 10:01:01.000 2005-10-03 00:00:00.000 Monday 2005-10-07 00:00:00.000 Friday 10.00 11.75 2007-05-08 15:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 1.50 11.75 2005-12-06 11:01:01.000 2005-12-05 00:00:00.000 Monday 2005-12-09 00:00:00.000 Friday 8.00 11.75 2006-01-06 12:01:01.000 2006-01-02 00:00:00.000 Monday 2006-01-06 00:00:00.000 Friday 7.00 11.75 2007-05-07 11:30:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 2.00 10.50 2007-05-08 13:30:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 1.00 10.00 2006-07-06 19:01:01.000 2006-07-03 00:00:00.000 Monday 2006-07-07 00:00:00.000 Friday 1.00 11.75 2007-05-09 14:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 1.25 10.00 2007-11-06 10:01:01.000 2007-11-05 00:00:00.000 Monday 2007-11-09 00:00:00.000 Friday 9.00 11.75 2007-05-10 14:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 5.00 10.00 2007-05-11 14:15:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 0.00 10.00 2007-05-12 15:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 0.00 10.00 2007-05-13 16:00:00.000 2007-05-14 00:00:00.000 Monday 2007-05-18 00:00:00.000 Friday 1.00 10.00 2007-05-08 05:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 1.00 10.00 2007-05-07 00:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 2.00 10.00 2007-05-14 11:00:00.000 2007-05-14 00:00:00.000 Monday 2007-05-18 00:00:00.000 Friday 3.00 10.00 2006-07-06 00:00:00.000 2006-07-03 00:00:00.000 Monday 2006-07-07 00:00:00.000 Friday 1.00 11.75 2007-05-11 13:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 3.50 10.00 2007-05-08 05:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 0.00 10.00 2007-05-08 07:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 2.00 10.00 2007-05-08 11:00:00.000 2007-05-07 00:00:00.000 Monday 2007-05-11 00:00:00.000 Friday 3.00 10.00 (57 row(s) affected)
- Line 2: datename(dw, sales_date): extracts the day of the week [dw] out of a date, any date. The day of the week is dependent of the locale defined by the installation of the SQL Server.
- Line 3: dateadd(wk, datediff(wk,0,sales_date), 0): Takes the sales_date and gets the week with datediff(wk,0,sales_date). Finally, it rewinds to the first day of the week [as defined by: set datefirst 1] which is Monday.
Getting the Mondays and Fridays for this week
- SELECT CURRENT_TIMESTAMP AS 'Today',
- DATENAME(dw,CURRENT_TIMESTAMP) AS "Today's day",
- DATEADD(wk, DATEDIFF(wk,0,CURRENT_TIMESTAMP), 0) AS 'Monday this week',
- DATENAME(weekday,DATEADD(wk, DATEDIFF(wk,0,CURRENT_TIMESTAMP), 0)) AS 'Day of the week',
- DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,CURRENT_TIMESTAMP), 0)) AS 'Friday this week',
- DATENAME(weekday,DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,CURRENT_TIMESTAMP), 0))) AS 'Day of the week'
- go
Today Today's day Monday this week Day of the week Friday this week Day of the week ----------------------- ------------------------------ ----------------------- ------------------------------ ----------------------- ------------------------------ 2007-10-07 16:49:07.873 Sunday 2007-10-08 00:00:00.000 Monday 2007-10-12 00:00:00.000 Friday (1 row(s) affected)
- Notice that I did not use the standard getdate() but current_timestamp.
- getdate() is MSSQL specific.
- current_timestamp is standard SQL. It's always better practice to use the standard SQL if possible, as Microsoft is trying to be more standard compliant.