CODEWITHSUNDEEP
sqlsql-server-2008
ghouse
ghouse

Reputation: 33

how to get week number for a date based on month from the date in sql server

I have a date column in a table and I want to get week number for that particular date based on the month from that date irrespective of the day

For example:

01-dec-2012 to 07-dec-2012 should give week number as 1
08-dec-2012 to 14-dec-2012 should give week number as 2
15-dec-2012 to 21-dec-2012 should give week number as 3
22-dec-2012 to 28-dec-2012 should give week number as 4
29-dec-2012 to 31-dec-2012 should give week number as 5

This week number is not dependent on the starting day of the week i.e, it can be any day

How can I write a select statement to get this output in SQL Server 2008?

Upvotes: 1

Views: 10680

Answers (3)

hd1
hd1

Reputation: 34677

Several options here to do what you wish. Most promising of which seems to be use of the DATEPART function. But do beware that results may differ depending on your local settings.

Hope one of them works out for you.

Upvotes: 0

SRIRAM
SRIRAM

Reputation: 1888

try this

 declare @dates datetime
 select @dates='2012-12-22'
 SELECT datepart(dd,@dates), ceiling (cast(datepart(dd,@dates)as numeric(38,8))/7)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use DAY (Transact-SQL)

select ((day(DateColumn)-1) / 7) + 1
from YourTable

SQL Fiddle

MS SQL Server 2012 Schema Setup:

create table YourTable
(
  D datetime
)

insert into YourTable 
select getdate()+Number
from master..spt_values
where type = 'P' and
      Number between 1 and 15

Query 1:

select D,
       ((day(D)-1) / 7) + 1 as W
from YourTable

Results:

|                              D | W |
--------------------------------------
| January, 03 2013 07:48:54+0000 | 1 |
| January, 04 2013 07:48:54+0000 | 1 |
| January, 05 2013 07:48:54+0000 | 1 |
| January, 06 2013 07:48:54+0000 | 1 |
| January, 07 2013 07:48:54+0000 | 1 |
| January, 08 2013 07:48:54+0000 | 2 |
| January, 09 2013 07:48:54+0000 | 2 |
| January, 10 2013 07:48:54+0000 | 2 |
| January, 11 2013 07:48:54+0000 | 2 |
| January, 12 2013 07:48:54+0000 | 2 |
| January, 13 2013 07:48:54+0000 | 2 |
| January, 14 2013 07:48:54+0000 | 2 |
| January, 15 2013 07:48:54+0000 | 3 |
| January, 16 2013 07:48:54+0000 | 3 |
| January, 17 2013 07:48:54+0000 | 3 |

Upvotes: 6

Related Questions