Link to home
Start Free TrialLog in
Avatar of jamppi
jamppiFlag for Sweden

asked on

sql weeknumber issue!


this is the query,

set datefirst 1;
select *
from sessions
where (datepart(wk, on_date_time) = 24)

It returns dates for week 23?  
what am i missing? (first day of year??)

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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. ...
Avatar of jamppi



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

well what is in on_date_time where you get that result
Avatar of jamppi



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

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
Avatar of jamppi


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.

e.g.: datepart(isowk, on_date_time) or datepart(isoww, on_date_time)
Caveat is that week starts on Monday regardless of DATEFIRST setting.
Avatar of jamppi


i got

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

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

Avatar of jamppi


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'
Avatar of jamppi


nope. for me week 24 is 13/6-19/6
Avatar of deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jamppi


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)
   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.

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.