mainrotor
asked on
How to transform Date to Week Of in SQL Server 2008
Hi all,
I have a SQL Server table that I want to use a SELECT query on.
The Selected table is tblORDER, and the field I want to query for is OrderDate. The Dates in the Query are saved in the following format:
7/8/2015
I want the result to say something like:
Week 2 of July
How can I do this?
Thank you in advance,
mrotor
I have a SQL Server table that I want to use a SELECT query on.
The Selected table is tblORDER, and the field I want to query for is OrderDate. The Dates in the Query are saved in the following format:
7/8/2015
I want the result to say something like:
Week 2 of July
How can I do this?
Thank you in advance,
mrotor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In this example the week "belongs" to the month in which it starts & weeks start on a Monday.
details:
| OrderNo | Week_String | OrderDate | week_in_year | month_of_order |
|---------|-------------|------------------------|--------------|----------------|
| abc121 | Week 5 June | June, 30 2015 00:00:00 | 27 | June |
| abc123 | Week 5 June | July, 01 2015 00:00:00 | 27 | July |
| abc124 | Week 1 July | July, 08 2015 00:00:00 | 28 | July |
| abc125 | Week 2 July | July, 15 2015 00:00:00 | 29 | July |
| abc126 | Week 3 July | July, 23 2015 00:00:00 | 30 | July |select
OrderNo
, 'Week ' + convert(varchar, CA.WeekNo )
+ ' '
+ datename(month,WeekStartingDt)
AS Week_String
, OrderDate
, datepart(week,OrderDate) week_in_year
, datename(month,OrderDate) month_of_order
from tblORDER
cross apply (
select datepart(day, datediff(day, 0, OrderDate)/7 * 7)/7 + 1
, dateadd(day,datediff(day, 0, OrderDate)/7 * 7,0)
) CA (WeekNo,WeekStartingDt)
order by OrderDatedetails:
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE tblORDER
([OrderDate] datetime, [OrderNo] varchar(6))
;
INSERT INTO tblORDER
([OrderDate], [OrderNo])
VALUES
('2015-06-30 00:00:00', 'abc121'),
('2015-07-01 00:00:00', 'abc123'),
('2015-07-08 00:00:00', 'abc124'),
('2015-07-15 00:00:00', 'abc125'),
('2015-07-23 00:00:00', 'abc126')
;
**Query 1**:
select
OrderNo
, 'Week ' + convert(varchar, CA.WeekNo )
+ ' '
+ datename(month,WeekStartingDt)
AS Week_String
, OrderDate
, datepart(week,OrderDate) week_in_year
, datename(month,OrderDate) month_of_order
from tblORDER
cross apply (
select datepart(day, datediff(day, 0, OrderDate)/7 * 7)/7 + 1
, dateadd(day,datediff(day, 0, OrderDate)/7 * 7,0)
) CA (WeekNo,WeekStartingDt)
order by OrderDate
**[Results][2]**:
| OrderNo | Week_String | OrderDate | week_in_year | month_of_order |
|---------|-------------|------------------------|--------------|----------------|
| abc121 | Week 5 June | June, 30 2015 00:00:00 | 27 | June |
| abc123 | Week 5 June | July, 01 2015 00:00:00 | 27 | July |
| abc124 | Week 1 July | July, 08 2015 00:00:00 | 28 | July |
| abc125 | Week 2 July | July, 15 2015 00:00:00 | 29 | July |
| abc126 | Week 3 July | July, 23 2015 00:00:00 | 30 | July |
[1]: http://sqlfiddle.com/#!3/ff2f47/12
oh, and here is an alternative method, again a normal week starts on a Monday, but with this method the week numbers re-start on he 1st of the month so week 1 in any Month starts with the first of that month regardless of which weekday that is.
datediff(week, dateadd(month, datediff(month, 0, OrderDate), 0), OrderDate) +1
datediff(week, dateadd(month, datediff(month, 0, OrderDate), 0), OrderDate) +1
| alternate_method | OrderDate |
|------------------|--------------------------|
| 3 | June, 20 2015 00:00:00 |
| 4 | June, 21 2015 00:00:00 |
| 4 | June, 22 2015 00:00:00 |
| 4 | June, 23 2015 00:00:00 |
| 4 | June, 24 2015 00:00:00 |
| 4 | June, 25 2015 00:00:00 |
| 4 | June, 26 2015 00:00:00 |
| 4 | June, 27 2015 00:00:00 |
| 5 | June, 28 2015 00:00:00 |
| 5 | June, 29 2015 00:00:00 |
| 5 | June, 30 2015 00:00:00 |
| 1 | July, 01 2015 00:00:00 |
| 1 | July, 02 2015 00:00:00 |
| 1 | July, 03 2015 00:00:00 |
| 1 | July, 04 2015 00:00:00 |
| 2 | July, 05 2015 00:00:00 |
| 2 | July, 06 2015 00:00:00 |
| 2 | July, 07 2015 00:00:00 |
| 2 | July, 08 2015 00:00:00 |
| 2 | July, 09 2015 00:00:00 |
| 2 | July, 10 2015 00:00:00 |
| 2 | July, 11 2015 00:00:00 |
| 3 | July, 12 2015 00:00:00 |
| 3 | July, 13 2015 00:00:00 |
| 3 | July, 14 2015 00:00:00 |
| 3 | July, 15 2015 00:00:00 |
| 3 | July, 16 2015 00:00:00 |
| 3 | July, 17 2015 00:00:00 |
| 3 | July, 18 2015 00:00:00 |
| 4 | July, 19 2015 00:00:00 |
| 4 | July, 20 2015 00:00:00 |
| 4 | July, 21 2015 00:00:00 |
| 4 | July, 22 2015 00:00:00 |
| 4 | July, 23 2015 00:00:00 |
| 4 | July, 24 2015 00:00:00 |
| 4 | July, 25 2015 00:00:00 |
| 5 | July, 26 2015 00:00:00 |
| 5 | July, 27 2015 00:00:00 |
| 5 | July, 28 2015 00:00:00 |
| 5 | July, 29 2015 00:00:00 |
| 5 | July, 30 2015 00:00:00 |
| 5 | July, 31 2015 00:00:00 |
| 1 | August, 01 2015 00:00:00 |
| 2 | August, 02 2015 00:00:00 |
| 2 | August, 03 2015 00:00:00 |
| 2 | August, 04 2015 00:00:00 |
| 2 | August, 05 2015 00:00:00 |
| 2 | August, 06 2015 00:00:00 |
CREATE TABLE tblORDER
([OrderDate] datetime, [OrderNo] varchar(6))
;
INSERT INTO tblORDER
([OrderDate])
VALUES
('2015-06-20 00:00:00'),
('2015-06-21 00:00:00'),
('2015-06-22 00:00:00'),
('2015-06-23 00:00:00'),
('2015-06-24 00:00:00'),
('2015-06-25 00:00:00'),
('2015-06-26 00:00:00'),
('2015-06-27 00:00:00'),
('2015-06-28 00:00:00'),
('2015-06-29 00:00:00'),
('2015-06-30 00:00:00'),
('2015-07-01 00:00:00'),
('2015-07-02 00:00:00'),
('2015-07-03 00:00:00'),
('2015-07-04 00:00:00'),
('2015-07-05 00:00:00'),
('2015-07-06 00:00:00'),
('2015-07-07 00:00:00'),
('2015-07-08 00:00:00'),
('2015-07-09 00:00:00'),
('2015-07-10 00:00:00'),
('2015-07-11 00:00:00'),
('2015-07-12 00:00:00'),
('2015-07-13 00:00:00'),
('2015-07-14 00:00:00'),
('2015-07-15 00:00:00'),
('2015-07-16 00:00:00'),
('2015-07-17 00:00:00'),
('2015-07-18 00:00:00'),
('2015-07-19 00:00:00'),
('2015-07-20 00:00:00'),
('2015-07-21 00:00:00'),
('2015-07-22 00:00:00'),
('2015-07-23 00:00:00'),
('2015-07-24 00:00:00'),
('2015-07-25 00:00:00'),
('2015-07-26 00:00:00'),
('2015-07-27 00:00:00'),
('2015-07-28 00:00:00'),
('2015-07-29 00:00:00'),
('2015-07-30 00:00:00'),
('2015-07-31 00:00:00'),
('2015-08-01 00:00:00'),
('2015-08-02 00:00:00'),
('2015-08-03 00:00:00'),
('2015-08-04 00:00:00'),
('2015-08-05 00:00:00'),
('2015-08-06 00:00:00')
;
select
alternate_method
, OrderDate
from tblORDER
cross apply (
select datepart(day, datediff(day, 0, OrderDate)/7 * 7)/7 + 1
, dateadd(day,datediff(day, 0, OrderDate)/7 * 7,0)
, datediff(week, dateadd(month, datediff(month, 0, OrderDate), 0), OrderDate) +1
) CA (WeekNo,WeekStartingDt,alternate_method)
order by OrderDateASKER
I haven't had time any of your suggestions yet.
mainrotor, do you still need help with this question?
I have an article out there called Build your own SQL calendar table to perform complex date expressions with downloadable code that allows you to define the weeks, and pull this off with a JOIN / function call to the table.