Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

On the Trail of the ISO Week Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2005 1:44 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #155189
Posted Wednesday, January 26, 2005 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 26, 2007 5:05 AM
Points: 7, 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

 

 

 




Post #157766
Posted Tuesday, March 1, 2005 2:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 25, 2006 1:37 AM
Points: 16, 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

Post #164611
Posted Wednesday, March 2, 2005 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 26, 2007 5:05 AM
Points: 7, 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




Post #164929
Posted Wednesday, March 2, 2005 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 26, 2007 5:05 AM
Points: 7, 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




Post #164966
Posted Wednesday, March 23, 2005 8:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:42 AM
Points: 251, Visits: 659
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.



Post #169572
Posted Tuesday, April 26, 2005 3:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2005 3:37 AM
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



Post #177385
Posted Monday, June 6, 2005 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2006 1:35 AM
Points: 2, 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
Post #187895
Posted Monday, July 18, 2005 3:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2005 10:02 AM
Points: 2, 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.

Post #201954
Posted Saturday, August 27, 2005 12:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 35,262, Visits: 31,747

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #214695
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse