Blog Archive

 

 About this blog

 
About this blog
Your Name 
SQL-Server

Peter Larsson

En snabb beräkning av veckonummer enligt ISO

2013-07-27 11:18:52 av Peter Larsson

Med SQL Server 2008 och senare kan man enkelt ta fram veckonumret enligt ISO med hjälp av DATEPART(ISO_WEEK, @Date).

Tyvärr finns det många produktionsmiljöer kvar som använder SQL Server 2005 eller till och med SQL Server 2000 och SQL Server 7. För dessa miljöer är det enda sättet att få fram veckonumret genom att skriva en egen funktion. Som alltid finns det bättre sätt att göra det på och det finns sämre sätt att göra det på. De flesta jag sett är dessutom beroende på användarinställningar som SET DATEFIRST eller SET LANGUAGE. Detta vill jag ha bort.

Jag tänkte idag gå igenom en ny algoritm som börjat sprida sig på nätet ganska nyligen. Det är en av de smartaste implementeringarna jag sett och som bygger på en idé jag bloggade om 2009. Den enkla lösningen ser ut såhär

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7 + 3, '17530101')) + 6) / 7

Tyvärr är den begränsad till datatypen DATETIME då Microsoft inte har implementerat DATE i alla deras datumfunktioner ännu. Är du intresserad av att se en komplett veckoberäkningsrutin för alla år mellan 1 och 9999 och som saknar alla beroenden av inbyggda funktioner ska du kolla här. Men eftersom DATE inte finns tillgänglig i SQL Server 2005 och tidigare så klarar vi oss bra med ovanstående kod.

Vecka nummer enligt ISO kan definieras på olika sätt. Antingen den vecka (måndag till söndag) som innehåller den första torsdagen vilket är den formella beskrivningen, eller den vecka som har minst fyra dagar på det nya året eller den vecka som innehåller den 4 januari. Vi kommer att använda oss av den formella beskrivningen och då har vi en utgångspunkt. Denna beräkning är känd och det gäller bara att vi hittar ett känt utgångsdatum som är en måndag. Datumet 17530101 är en måndag och det är också det äldsta datum som DATETIME hanterar, så detta passar oss alldeles utmärkt. Vad vi också måste passa oss för är att vi väljer ett utgångsdatum som är en måndag den 1 januari som INTE är ett skottår. Jag går inte in på matematiken här, det skulle ta alldeles för mycket plats. Således skulle inte 17540101 fungera. Återigen passar datumet 17530101 in eftersom 1753 inte är ett skottår.

Vi börjar med att räkna fram hur många dagar som passerat sedan 17530101 genom att skriva

DATEDIFF(DAY, '17530101', @Date)

Då får vi ett heltal som är 95170 för datumet 20130727. Det har alltså passerat 95 170 dagar sedan 17530101. Efter detta förlitar vi oss på heltalsdivision i SQL Server. En vecka är som bekant 7 dagar och vi dividerar antalet passerade dagar med 7 och behåller heltalsdelen. Sedan multiplicerar vi med 7 igen för att få fram måndagen i den innevarande veckan genom att skriva följande

DATEDIFF(DAY, '17530101', @Date) / 7 * 7

Då får vi fram ett värde 95165 som är måndagen den 22 juli 2013. Här använder jag mig inte av

DATEDIFF(WEEK, '17530101', @Date)

eftersom denna är beroende av SET DATEFIRST. Prova med ett datum som är en söndag så får du se skillnaden mellan de två formlerna. Men nu skulle vi koncentrera oss på den första torsdagen så vi behöver addera tre dagar till måndagen för att komma till torsdag och detta gör vi enkelt genom att skriva

DATEDIFF(DAY, '17530101', @Date) / 7 * 7 + 3

Då har vi värdet 95168 som motsvarar datumet torsdag 20130725. Detta antal dagar adderar vi nu till vårt utgångsdatum 17530101 genom att använda DATEADD för att få fram vårt torsdagsdatum i innevarande vecka

DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7 + 3, '17530101')

Så här långt är det egentligen inget märkvärdigt med algoritmen. Det är med DATEPART vi börjar se hur smart algoritmen är. Vad denna gör är att ta fasta på “Veckonumret kan beräknas genom att räkna antalet torsdagar; vecka 12 innehåller den tolfte torsdagen i året.” och det är här som DATEPART kommer till vår räddning!

Det som vi först måste komma överens om är att alla torsdagar infinner sig på sju dagars intervall. Den torsdag som infaller mellan den 1 januari och 7 januari (1 till 7) enligt DATEPART, kan beräknas med följande och ska få veckonummer 1.

DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7 + 3, '17530101'))

Detta är också sant för 20130726 (vecka 30), som har den den trettionde torsdagen i året. För att få fram veckonumret kan vi antingen förlitar vi oss på heltalsdivision igen genom att först dra ifrån 1 (0 till 6), dividerar med 7 och lägger till 1 (veckor startar alltid med 1) vilket blir tre operationer, eller så adderar vi 6 (7 till 13) och dividerar med 7 vilket bara är två operationer.

Då kommer den kompletta algoritmen att se ut som

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7 + 3, '17530101')) + 6) / 7

och den fungerar för alla datum som ryms i en DATETIME.

Vill man spara in ännu en operation i algoritmen kan man ta bort “+ 3” genom att istället addera antalet sjudagarsintervaller till en torsdag direkt, såhär

 

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101', @Date) / 7 * 7, '17530104')) + 6) / 7

 

Lycka till!

Comments

No Comments
Submit Comments
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: