Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


On the Trail of the ISO Week


On the Trail of the ISO Week

Author
Message
Chris Hedgate
Chris Hedgate
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1643 Visits: 7
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/onthetrailoftheisoweek.asp

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
John Valore
John Valore
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

Declare @startdate as smalldatetime
set @startdate ='2005-27-01'

SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr,
CASE
-- Exception where @startdate is part of week 52 (or 53) of the previous year
WHEN @startdate <
CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
END
THEN
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
END,
@startdate
) / 7) + 1
-- Exception where @startdate is part of week 1 of the following year
WHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
END
THEN 1
ELSE
-- Calculate the ISO week number for all dates that are not part of the exceptions above
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
END,
@startdate
) / 7) + 1
END AS IsoWeek

A bit puzled about this

Best regards

John Valore





Nic Washington
Nic Washington
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1

I tried John's code - I had to change the @StartDate to '2005-01-27'.

I had no problems - I received an ISO Week of "4"

Regards,

Nic Washington


John Valore
John Valore
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

Thanks for the reply Nic,

I took some time before someone responded, so I had actually abandoned the approach and settled with the standard datepart function.

Thanks to your response, I looked at the problem again and have now solved the problem. I still have to input the date in the format yyyy-mm-dd :

set @startdate =CAST('2005-01-27' as smalldatetime)

but if I include a statement :
set dateformat mdy

(default american date input/output), the code from Chris Hedgate now works.

The devil is apparently still in the details and the several datetime formats.

Thank you very much.

John Valore





John Valore
John Valore
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

Hi,

Just an update.

I was going to use the functionality in an udf environment, but "forgot" that it is not possible to issue SET commands in an udf.

So I had to look at Chris code again when it suddenly strooke me, that I should replace all the '-01-04' with '-04-01'.

Bingo.

John Valore





Henk Schreij
Henk Schreij
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 831
Cris:
If you use the ISO format for dates (yyyymmdd), not the yyyy-mm-dd format, SQL Server is not vulnerable for myd or dmy dates.
See CONVERT ISO 112 (or 12 for yymmdd) in BOL.

SQL server accepts '20050323' (today, y=2005, m=03, d=23) either in Europe or USA.



rkintrup
rkintrup
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Here is a version which uses CONVERT to allow using the yyyy-mm-dd format. This makes the code independent from SQL Server installation flavors.

Ralf

-----

DECLARE @date datetime
SET @date = CONVERT(datetime, '2003-12-31', 120) -- 1
--SET @date = CONVERT(datetime, '2005-01-01', 120) -- 53
--SET @date = CONVERT(datetime, '2005-01-05', 120) -- 1
--SET @date = CONVERT(datetime, '2006-01-01', 120) -- 52

SELECT @date,
CASE
-- Exception where @date is part of week 52 (or 53) of the previous year
WHEN @date <
CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7
WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)
WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '-01-04')
END
THEN (DATEDIFF(d,
CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7
WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)
WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120))
END,
@date) / 7) + 1
-- Exception where @date is part of week 1 of the following year
WHEN @date >=
CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7
WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)
WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))
END
THEN 1
-- Calculate the ISO week number for all dates that are not part of the exceptions above
ELSE (DATEDIFF(d,
CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7
WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)
WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))
END,
@date) / 7) + 1
END AS IsoWeek



Tomasz Kubacki
Tomasz Kubacki
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
Hmmm that is my function similiar to above but IMHO easier to understand

CREATE FUNCTION ISOweek(@date datetime)
RETURNS int
AS
BEGIN
declare @dayOfWeek int
declare @1ThISOWeek1ThDay datetime
declare @4ThJan datetime
declare @1ThJan datetime
declare @31ThDec datetime

declare @numberOfISOweeks int
declare @tmp int
declare @returnValue as int

set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-04' as datetime)
set @1ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-01' as datetime)
set @31ThDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '-12-31' as datetime)

--"rule of thursday"
if (DATEPART(dw, @1ThJan) = 5 or DATEPART(dw, @31ThDec) = 5 )
set @numberOfISOweeks = 53
else
set @numberOfISOweeks = 52

set @dayOfWeek = (select DATEPART(dw, @4ThJan) - 1)
if @dayOfWeek = 0
set @dayOfWeek = 7

set @1ThISOWeek1ThDay = (select DATEADD(day, -(@dayOfWeek -1) ,@4ThJan))

if @date < @1ThISOWeek1ThDay
BEGIN
if (DATEPART(dw, DATEADD(year,-1, @1ThJan)) = 5 or DATEPART(dw,DATEADD(year,-1, @31ThDec)) = 5 )
set @returnValue = 53
else
set @returnValue = 52

return @returnValue -- or -1 as an error
END

--number of days between 1th day of the 1th ISO week and Jan 1

set @tmp = (select DATEDIFF(day,@1ThJan , @1ThISOWeek1ThDay)) + 1

set @returnValue = (DATEPART(dy,@date)- @tmp ) / 7 + 1

-- if number of week is greater than number of ISO weeks in current year it means
-- that the date is in the firth week of next year

if @returnValue > @numberOfISOweeks
set @returnValue = 1 -- or -1 as an error

return @returnValue

END
requena
requena
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1

Sombody now how to calculate first monday day from given week

For example I need get the day from a given week:

ISO date ‘20050101’ is the week 53 in ISO 8601

If a have the week number 53 how I can calculate the ISO date?

Thank for all in advance.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55504 Visits: 40405

Better late than never, I guess...

DECLARE @Year CHAR(4)
DECLARE @NextYear CHAR(4)
DECLARE @ISOWeek VARCHAR(2)

SET @Year = '2001'
SET @ISOWeek = '53'
SET @NextYear = @Year+1

SELECT
CASE
WHEN @ISOWeek > 0
AND DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)
< DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@NextYear),0)
THEN DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)
ELSE 0
END AS StartDate,
DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate

--todo... needs some error checking for the week number...

It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...

I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search