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
mainrotorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
How to you define the week number?  SQL 2008 does not have a DATEPART value for week in month.

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.
PortletPaulEE Topic AdvisorCommented:
There is some guesswork needed to answer the existing question.

You can remove the need for guesswork by doing 2 simple things.
a. provide some "sample data" from your table(s), and
b. supply the "expected result" i.e. what you want to see as the outcome

For example, here is some data:
CREATE TABLE tblORDER
    ([OrderDate] datetime, [OrderNo] varchar(6))
;
    
INSERT INTO tblORDER
    ([OrderDate], [OrderNo])
VALUES
    ('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')
;

Open in new window

and from that I can get this result:
| OrderNo | week_in_year | no_of_orders |
|---------|--------------|--------------|
|  abc123 |           27 |            1 |
|  abc124 |           28 |            1 |
|  abc125 |           29 |            1 |
|  abc126 |           30 |            1 |
        

Open in new window

Which I can achieve by this query:
select
        OrderNo
      , datepart(week,OrderDate) week_in_year
      , count(*) no_of_orders
from tblORDER
group by
        OrderNo
      , datepart(week,OrderDate)

Open in new window


You can even interact with this example at sqlfiddle: http://sqlfiddle.com/#!3/b5441/2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
In this example the week "belongs" to the month in which it starts & weeks start on a Monday.
    | 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 |

Open in new window

That result produced by:
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

Open in new window


details:
**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

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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
| 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 OrderDate

Open in new window

mainrotorAuthor Commented:
I haven't had time any of your suggestions yet.
Vitor MontalvãoMSSQL Senior EngineerCommented:
mainrotor, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.