SQL Server date function

I need to get the week number of the giving date. For Ex jan 1 = week n开发者_如何学Goo is 1, Jan 8 - week 2 like this.. any one help me out pls.

You should try something like this:

DECLARE @Dt datetime
SELECT @Dt='02-21-2008'
SELECT DATEPART( wk, @Dt)

This should return the weeknumbers you want.

SQL Server starts counting from the 1st of january. If you want to return the ISO weeknumbers, you need to do a bit more scripting. A nice howto is listed in this site: http://www.rmjcs.com/SQLServer/TSQLFunctions/ISOWeekNumber/tabid/207/Default.aspx

MSDN: DATEPART (Transact-SQL)

In response to Robin's comment:

But i need in such a way, that from jan 1 to 7, it should return 1, from jan 8 to 17 it should return 2 like this.. hope u got my impression

In that case you could also write something like this.

select (datepart(dy, '2011-01-01') / 7) + 1
--returns 1
select (datepart(dy, '2011-01-02') / 7) + 1
--returns 1
select (datepart(dy, '2011-12-31') / 7) + 1
--returns 53

I don't know how SQL Server 2008 responds with the iso_week and wk parameter as I only got a SQL 2005 instance available at the moment.

Does this do what you want?

declare @T table (dt datetime)
insert into @T values
('2010-12-31'),
('2011-01-01'),
('2011-01-02'),
('2011-01-03'),
('2011-01-04'),
('2011-01-05'),
('2011-01-06'),
('2011-01-07'),
('2011-01-08')

select
  dt,
  (datediff(d, dateadd(year, datediff(year, 0, dt), 0), dt) / 7)+1
from @T

Result

dt                      
----------------------- -----------
2010-12-31 00:00:00.000 53
2011-01-01 00:00:00.000 1
2011-01-02 00:00:00.000 1
2011-01-03 00:00:00.000 1
2011-01-04 00:00:00.000 1
2011-01-05 00:00:00.000 1
2011-01-06 00:00:00.000 1
2011-01-07 00:00:00.000 1
2011-01-08 00:00:00.000 2