sql weeknumber issue!

what is the data you get? data type, values?
note that the first days of the year are usually considered to be the part of the last week of previous year. ...

Hi,

the datatype is DATETIME,
it resturns dates from 2011-06-12 and descending.

/J

well what is in on_date_time where you get that result

ex,

2011-06-12 21:00:00:000

/J

I make it that if the first week is week 1, and that is the 1st and 2nd Jan, before week 2 starting monday 3/1/2011

then the date you gave is in the week 22 whole weeks after 3/1/2011 and is therefore week 24

the set command you use makes monday 1st day of week

So, what would be the solution to this?

--try it for 1st 2nd 3rd january, then show that the week is 22 whole weeks after start of week 2

set datefirst 1;
select datepart(wk, '2011-06-12'), datepart(wk, '01-01-2011'),datepart(wk, '01-02-2011'),datepart(wk, '01-03-2011'), datediff(day,'01-03-2011','2011-06-12')/7.0;

--week 24 starts on 6/6
select datepart(wk, '2011-06-05'),datepart(wk, '2011-06-06')

If you have SQL 2008 or higher, you can use ISOWK.
http://msdn.microsoft.com/en-us/library/ms174420(v=SQL.100).aspx

e.g.: datepart(isowk, on_date_time) or datepart(isoww, on_date_time)

Caveat is that week starts on Monday regardless of DATEFIRST setting.

i got

25 1 2 -22.857142 on the first query
and the second
24 24


select datepart(wk,'2011-06-16')  gives 25 ??

i'm running sql 2005

so you expect week 24 to be 06/06 - 12/06?

in that case select what SQL server calls 'week 23'

nope. for me week 24 is 13/6-19/6

that seems to work!

For SQL 2005 or less, you can try this if you need inline SQL:

-- 1900-01-01 is a Monday, so need -1 for Sunday
-- -1 from start of year, to do calculation accurately at start of year
SELECT DATEDIFF(wk, DATEADD(yy, DATEDIFF(yy, 0, StartOfWeek), 0)-1, StartOfWeek)
FROM (
   SELECT DATEADD(wk, DATEDIFF(wk, 0, on_date_time), 0)-1 AS StartOfWeek
) derived

You can replace instances of StartOfWeek with the highlighted formula in the derived query, so this can be done in one expression.  Probably can shorten the formulas if thought hard at it.

For example, using the fact that a YYYY string will convert to DATETIME correctly as YYYY-01-01, this can be used to replace the DATEADD(..., DATEDIFF(...)) syntax for the start of year calculation.

DATEADD(DD, -1, DATENAME(YY, StartOfWeek))

Guess I missed the other posts, sorry -- browser problem.  My post was to simulate ISO week which will return 2011-01-01 with week #52.  The accepted solution works very nicely, but note that on January 1st, you will get 0.