Kalenderwoche ermitteln wo der erste Tag Montag und die Woche vollständig sein muss ?
Volkan Senguel
welches die ISO Kalenderwoche ermittelt was auch soweit funktioniert.
Nun haben wir hier noch eine alte (schon angestaubte) Access DB wo wir die
Reports nun ins Reporting Service migrieren was auch eigentlich kein Problem
ist.
Nur ist in Access bei den Abfragen diese Methode benutzt worden
"=DatTeil("ww";[DATUMS_FELD];2;3)" womit definiert wird dass die Woche mit
Montag startet und die 1. KW vollständig sein muss.
Die function in SQL habe ich dazu diese hier gefunden:
CREATE PROC dbo.ISOweek (
@DATE datetime
)
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek = DATEPART(wk, @DATE) + 1 -
DATEPART(wk, CAST( DATEPART( yy, @DATE ) AS CHAR(4) ) + '0104' )
--Special cases: Jan 1-3 may belong to the previous year
IF ( @ISOweek = 0 )
BEGIN
DECLARE @Date2 datetime
SET @DATE2 = CAST( DATEPART( yy, @DATE ) - 1 AS CHAR(4) ) +
'12' + CAST( 24 + DATEPART( DAY, @DATE ) AS CHAR(2) )
-- Nicht so doll: rekursiver Aufruf
EXEC @ISOWeek = ISOWeek @DATE2
SET @ISOWeek = @ISOWeek + 1
END
--Special case: Dec 29-31 may belong to the next year
IF ( ( DATEPART( mm, @DATE ) = 12 ) AND
( ( DATEPART( dd, @DATE ) - DATEPART( dw, @DATE) ) >= 28 ) )
SET @ISOweek=1
RETURN(@ISOweek)
END
Nur werden nicht die gleichen KWs ausgegeben wie in Access.
Wie kriege ich das hin, gibt es hier ein Beispiel dazu?
Danke im Voraus für eine Tip
Volkan Senguel
Christa Kurschat
"Volkan Senguel" <volkan....@networkpixel.com> schrieb im Newsbeitrag
news:utj2hFIf...@TK2MSFTNGP06.phx.gbl...
> Hallo zusammen ich habe für unseren SQL 2005 ein paar Beispiele gefunden
> welches die ISO Kalenderwoche ermittelt was auch soweit funktioniert.
>
> Nun haben wir hier noch eine alte (schon angestaubte) Access DB wo wir die
> Reports nun ins Reporting Service migrieren was auch eigentlich kein
> Problem ist.
> Nur ist in Access bei den Abfragen diese Methode benutzt worden
> "=DatTeil("ww";[DATUMS_FELD];2;3)" womit definiert wird dass die Woche mit
> Montag startet und die 1. KW vollständig sein muss.
>
> Die function in SQL habe ich dazu diese hier gefunden:
Das ist auch genau die richtige.
...
>
> Nur werden nicht die gleichen KWs ausgegeben wie in Access.
>
> Wie kriege ich das hin, gibt es hier ein Beispiel dazu?
>
Führe mal ein select @@datefirst auf dem Server aus und dann schau mal in
der Hilfe nach set Datefirst.
Vermutlich hast Du da eine andere Einstellung
Gruß
Christa
--
Access-FAQ: http://www.donkarl.com
SQL-Server-FAQ: www.sqlfaq.de
InsideSql: www.insidesql.org
Volkan Senguel
Ich habe mal dies als Testabfrage eingegeben:
SET DATEFIRST 1
SELECT @@DATEFIRST AS DATEFIRST
DECLARE @DATE DATETIME
SET @DATE = '2004-08-22'
SELECT dbo.ISOweek(@DATE) AS ISOweek, DATEPART(DW,@DATE) AS DP, @DATE AS
[Date]
Dies müsste laut Outlook Kalender und der Access DB die KW 33 sein.
erhalte aber dies hier:
DATEFIRST: 1
ISOWeek: 34
???
Weiss nicht woran es liegen könnte :(
Volkan
Hier noch die function:
USE [TestDB]
GO
/****** Object: UserDefinedFunction [dbo].[ISOWeek] Script Date:
01/22/2009 13:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ISOWeek]
(
@DATE datetime
)
RETURNS INT
AS
BEGIN
DECLARE @ISOweek int, @WeekDay int
SET @WeekDay = DATEPART(DW,@DATE)
SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST( DATEPART(
yy, @DATE ) AS CHAR(4) ) + '0104' )
--Special cases: Jan 1-3 may belong to the previous year
IF ( @ISOweek = 0 )
BEGIN
DECLARE @Date2 datetime
SET @DATE2 = CAST( DATEPART( yy, @DATE ) - 1 AS CHAR(4) ) + '12' +
CAST( 24 + DATEPART( DAY, @DATE ) AS CHAR(2) )
-- Nicht so doll: rekursiver Aufruf
EXEC @ISOWeek = ISOWeek @DATE2
SET @ISOWeek = @ISOWeek + 1
END
--Special case: Dec 29-31 may belong to the next year
IF ( ( DATEPART( mm, @DATE ) = 12 ) AND
( ( DATEPART( dd, @DATE ) - DATEPART( dw, @DATE) ) >= 28 ) )
SET @ISOweek=1
RETURN(@ISOweek)
END
"Christa Kurschat" <christa_kur...@web.de> schrieb im Newsbeitrag
news:Or$wypIfJ...@TK2MSFTNGP06.phx.gbl...
Christa Kurschat
"Volkan Senguel" <volkan....@networkpixel.com> schrieb im Newsbeitrag
news:%23YMuIDJ...@TK2MSFTNGP04.phx.gbl...
> Danke Christa für deine Antwort
>
> Ich habe mal dies als Testabfrage eingegeben:
>
> SET DATEFIRST 1
> SELECT @@DATEFIRST AS DATEFIRST
> DECLARE @DATE DATETIME
> SET @DATE = '2004-08-22'
> SELECT dbo.ISOweek(@DATE) AS ISOweek, DATEPART(DW,@DATE) AS DP, @DATE
> AS [Date]
>
> Dies müsste laut Outlook Kalender und der Access DB die KW 33 sein.
> erhalte aber dies hier:
> DATEFIRST: 1
> ISOWeek: 34
Nein, nicht wirklich.
Das ISO-Format bestimmt die erste KW als die Woche, in der die ersten 4 Tage
des Jahres liegen, im Gegensatz zur Zählung in den uSA, wo die 1. KW di
eWoche ist, in die der 01.01. fällt..
Deine Acc-Abfrage fängt mit der 1 vollen Woche als 1. KW an.
Wenn Du genau das brauchst, mußt Du selbst noch tätig werden und eine eigene
Funktion schreiben.
Dein oberes Beispiel stimmt mit der Acc-Version
?Datepart("ww","22.08.2004",2,2) überein.
Ergebnis: 34
Volkan Senguel
Kannst du mir helfen um die function zu ändern damit ich
"=DatTeil("ww";[DATUMS_FELD];2;3)" erhalte?
Danke
Volkan
"Christa Kurschat" <christa_kur...@web.de> schrieb im Newsbeitrag
news:%23AzWV$JfJHA...@TK2MSFTNGP03.phx.gbl...
Klaus Oberdalhoff
da wirst du dir selber was schreiben müssen, denn ihr habt in Access durch
die fehlerhalfte Definition bei
> "=DatTeil("ww";[DATUMS_FELD];2;3)"
eine absolut keiner Norm entsprechende Wochennummerierung
Korrekt wäre es, wenn du in Access deine Definition auf
"=DatTeil("ww";[DATUMS_FELD];2;2)"
änderst - und nicht versuchst, eine fehlerhafte Datumsberechnung quasi
"weiterzuschleifen"
das ist genauer im meinem Datumsblabla erklärt ....
Die Funktion von SQL Server MVP und Guru Elmar (Boye) ist schon die
richtige, und würde, wenn du in
Access das Wochendatum richtig berechnen würdest, auch identische Ergebnisse
liefern <g>
============
Aha, es ist mal wieder Zeit für mein Datums-bla bla
erweitert um die SQL Server Funktion ISOWeek von Elmar ...
============
In Access werden die Datumsfelder intern definitv als Zahl Typ Double
gespeichert, wobei der Vorkomma-Anteil (positiv wie negativ) die Anzahl der
Tage seit dem 30.12.1899 bedeuten und der Nachkommateil die "Fraction" des
Tages, d.h. die Uhrzeit. (0,5 = 12 Uhr) Zur Anzeige des Datums rechnet
Access intern das Datum immer um... (D.h. es wird IMMER ein "Zeitpunkt"
gespeichert)
Wenn "NUR" die Uhrzeit abgespeichert wird, ist die Tagesanzahl
(Vorkommateil) gleich 0 und umgekehrt, bei Datum ohne Uhrzeit ist der
Nachkommaanteil immer 0, d.h. Mitternacht. ...
Zweistellig angezeigt wird das Jahr des Datums deswegen, weil Access
standardmäßig (wenn kein Extra-"Format" gesetzt wurde) zur Anzeige des
Datums den Wert, der in der Systemsteuerung unter "Ländereinstellungen" Tab
Datum unter "Kurzes Datumsformat" eingestellt ist, verwendet.
Für diese Umrechnung gibt es diverse accessinterne Datums- und
Uhrzeitfunktionen (siehe Onlinehilfe), als da beispielsweise wären:
DateSerial
TimeSerial
Year
Month
Day
DateDiff
DateAdd
DatePart
Format (Parameter firstFourDays und vbMonday nicht vergessen)
Now
Weekday (Parameter vbMonday nicht vergessen)
=================================
> Aber ich bräuchte eine Funktion, die zu einem bestimmten Datum ausrechnet,
> in welche KW dieses Datum fällt.
Das geht immer mit dem Format-Statement (siehe Online-Hilfe)
=================================
> Aber ich bräuchte das Datum in einem SQL-String
Für SQL musst du immer das Datum in einen "String" umwandeln.
Hier helfen zwei kleine Funktionen weiter, die der KnowHow.mdb (siehe
Signatur) entnommen wurde
Wenn der Uhrzeit-Teil (wie bei der Funktion SQLDatum) fehlt, wird immer
Mitternacht (0 <Null> Uhr 0 <Null> Minuten und 0 <Null> Sekunden)
angenommen, wenn der UhrzeitTeil benötigt wird, so muss die Funktion
DateTimeForSQL verwendet werden. (Siehe auch Access-FAQ
http://www.donkarl.com Punkt 3.13)
Function SQLDatum(Datumx) As String
'Macht aus irgendeinem gültigen Datum einen String #yyyy-mm-dd# (ISO-Norm
Datum)
If IsDate(Datumx) Then
SQLDatum = Format(CDate(Datumx), "\#yyyy\-mm\-dd\#", vbMonday,
vbFirstFourDays)
Else
SQLDatum = ""
End If
End Function
Function DateTimeForSQL(dteDate) As String
'Datum incl. Uhrzeit für SQL und INI-Files als String
' DateTimeForSQL = Format(CDate(dteDate), "\#yyyy\-mm\-dd h:nn:ss AM/PM
\#", vbMonday, vbFirstFourDays)
DateTimeForSQL = Format(CDate(dteDate), "\#yyyy\-mm\-dd hh:nn:ss\#",
vbMonday, vbFirstFourDays)
End Function
=================================
> Ist es richtig, dass die erste Kalenderwoche immer die erste Woche im Jahr
> mit mindestens 4 Tagen ist?
> Wann fängt die Kalenderwoche an? Montag, oder Sonntag?
Das ist weltweit nicht einheitlich geregelt.
Für WestEuropa (d.h. zumindest für alle Staaten der EG sowie der Schweiz)
gilt irgendeine ISO-Norm seit den 1970-er Jahren des letzten Jahrtausends
Diese ISO-Norm besagt, daß für alle Staaten, die dieser Norm "beigetreten"
sind, gilt, daß die WochenNr mit der Nummer 1 (In Worten: "eins") die Woche
ist, die vier Tage hat, bzw. (was das Gleiche ist) die den Donnerstag
beinhaltet (vbFirstFourDays). In dieser ISO-Norm ist auch festgelegt, daß
der erste Tag der Woche der Montag (und nicht wie davor der Sonntag) ist.
(vbMonday) Der erste Tag der Woche ist vorallem für Kalenderhersteller
wichtig, da in dieser Norm auch drinsteht, daß der erste Tag der Woche als
erstes in der Kalendertageaufstellung stehen sollte. (also Mo Di Mi ... und
nicht So Mo Di ...)
In den USA gilt beides NICHT, dort ist immer die Woche die Nr 1 (in
Worten:eins), die den ersten Januar beinhaltet und der erste Tag der Woche
ist der Sonntag.
Wie es die anderen Staaten (die z.Tl. noch nach einem ganz anderen Kalender
!!! rechnen wie China oder Israel oder so) halten, ist mir nicht bekannt.
Auch für Rußland und Indien etc. weiß ich es nicht. Wenn du also für deine
Applikation das richtige tun willst, hast du folgende Möglichkeiten:
Du verwendest beim Format immer den Parameter adUseSystem oder du fragst es
explizit in deinem Programm ab oder du weißt, in welcher "Hemisphäre" dein
Programm verwendet wird und verwendest diese Einstellung fix. (ich verwende
z.B. in der KnowHow die europäische Norm fix)
Die in dem anderen Posting gezeigte Methode:
> KW = Format(Datum,"ww",2,3)
ist definitiv für Europa und USA falsch, entweder
Europäisch, aber besser Konstantennamen verwenden, also
> KW = Format(Datum,"ww", vbMonday, VbFirstFourDays)
> KW = Format(Datum,"ww",2,2)
oder in den USA, d.h. amerikanisch:
> KW = Format(Datum,"ww", VbSunday, VbFirstJan1)
> KW = Format(Datum,"ww", 1, 1)
> KW = Format(Datum,"ww") - Kurzform da default
oder Systemeinstellung verwenden:
> KW = Format(Datum,"ww", vbUseSystem, vbUseSystem)
> KW = Format(Datum,"ww", 0, 0)
===========================================
ACHTUNG: vbUseSystem ist NICHT die Defaulteinstellung, sondern 1,1 !!!
===========================================
Aus dem gleichen Grund ist bei den Pivot-(Kreuztabellen)-Formularen in
Access (ab Acc 2003) Vorsicht geboten, da die dort angebotene Wochen-
Pivotierung sich IMMER auf die AMERIKANISCHE Wochennummer bezieht und
daher in Westeuropa UNBRAUCHBAR ist. MS ist dieser Fehler bekannt, er
wird aber weder in Access 2003 noch im kommenden Access 2007 behoben
werden.
Ebenso findet sich in der KnowHow (oder als neuere Version ein Extrakt
in der Datei ObdKalender.zip bei www.freeaccess.de) ein komplettes
Kalendermodul, mit dem man auch eigene Feier- und Werktagsberechnungen
vornehmen kann. (Beschreibung dazu im Inneren des Moduls
mdlSonstigesDatumUhrzeit)
In diese Gruppe gehört die UmkehrFunktion Fkt_KWMon aus dem Modul
mdlSonstigesDatumUhrzeit, mit deren Hilfe man den Montag einer
(europäischen) Kalenderwoche berechnen kann, das ich aus der Access-FAQ
entlehnt habe ...
für den SQL Server
CREATE PROC dbo.ISOweek (
@DATE datetime
)
AS
BEGIN
-- Autor: Elmar Boye
DECLARE @ISOweek int
SET @ISOweek = DATEPART(wk, @DATE) + 1 -
DATEPART(wk, CAST( DATEPART( yy, @DATE ) AS CHAR(4) ) + '0104' )
--Special cases: Jan 1-3 may belong to the previous year
IF ( @ISOweek = 0 )
BEGIN
DECLARE @Date2 datetime
SET @DATE2 = CAST( DATEPART( yy, @DATE ) - 1 AS CHAR(4) ) +
'12' + CAST( 24 + DATEPART( DAY, @DATE ) AS CHAR(2) )
-- Nicht so doll: rekursiver Aufruf
EXEC @ISOWeek = ISOWeek @DATE2
SET @ISOWeek = @ISOWeek + 1
END
--Special case: Dec 29-31 may belong to the next year
IF ( ( DATEPART( mm, @DATE ) = 12 ) AND
( ( DATEPART( dd, @DATE ) - DATEPART( dw, @DATE) ) >= 28 ) )
SET @ISOweek=1
RETURN(@ISOweek)
END
Und der Test
DECLARE @week int
DECLARE @date datetime
SET @date = '20000101'
EXEC @Week = ISOWeek @date
-- Noch die 52. Woche
SELECT @Week
SET @date = '20000103'
EXEC @Week = ISOWeek @date
-- die 1.
SELECT @Week
Frage:
SELECT cast (dbo.ISOweek('31.12.2005') as varchar) +' '+
cast((datepart(year,'31.12.2005')) as varchar) AS 'ISO Week year'
SELECT cast (dbo.ISOweek('1.1.2006') as varchar) +' '+
cast((datepart(year,'1.1.2006')) as varchar) AS 'ISO Week year'
Wenn man diese beiden Statements abfeuert bekommst man beim ersten 52
2005 (korrekt). Beim zweiten jedoch 52 2006 (nicht korrekt) es müsste
an dieser Stelle ja 52 2005 heissen.
Antwort:
Das ergibt sich nunmal aus der 4-Tage Regel, die in Europa
angewendet wird. Und DATEPART kennt die ebenfo für Jahre nicht.
Eine Variante wie wäre unten stehende Funktion. Falls Du
zwei Spalten brauchst, wäre das durch eine Tabellenrückgabe
zu machen.
///
CREATE FUNCTION dbo.fn_isoweekyear
( @datevalue datetime )
RETURNS int
/***
Autor: Elmar Boye
Parameter: datevalue = Datum
Return: int (Jahr + Woche, yyyyww)
Description:
Liefert die ISO Kalenderwoche (1. 4-Tage Woche im Jahr)
zusammen mit dem Kalenderjahr in der Form 'yyyyww' als integer
***/
BEGIN
DECLARE @week int
SET @week =
YEAR(@datevalue) * 100 +
+ ( DATEPART(wk, @datevalue) + 1
- DATEPART( wk, CAST( DATEPART( yyyy, @datevalue) AS char(4) ) + '0104' ) )
-- Januar 1 - 3 können zum Vorjahr gehören
IF (@week % 100) = 0
SET @week = dbo.fn_isoweekyear(
CAST( DATEPART( yy, @datevalue) - 1 AS char(4) ) + '12'
+ CAST( 24 + DATEPART( dd, @datevalue ) AS char(2) ) ) + 1
-- Dezember 29 - 31 können zum Folgejahr gehören
IF ( ( DATEPART( mm, @datevalue ) = 12) AND
( ( DATEPART( dd, @datevalue ) - DATEPART(dw, @datevalue ) ) >= 28) )
SET @week = (YEAR(@datevalue) + 1) * 100 + 1
RETURN(@week)
END
\\\
mfg Klaus
Elmar Boye
Klaus Oberdalhoff schrieb:
> Die Funktion von SQL Server MVP und Guru Elmar (Boye) ist schon die
> richtige,
Korrektur:
Ich bin _nicht_ der Autor von ISOWEEK (und auch nix Guru ;-)
Die ursprüngliche Version stammt aus der ersten
SQL Server FAQ von Neil Pike (SQL MVP der ersten Stunde).
Noch erhältlich über (wenn auch lange nicht mehr aktualisiert):
<URL:http://www.mssqlserver.com/faq/> (dort Weeknumber.rtf)
Daraus habe ich sie einige Male dem deutschen Publikum bekannt
gemacht.
Und sie hat Eingang in die SQL Server 2000 Online Dokumentation
und folgende gefunden.
Gruß Elmar
Klaus Oberdalhoff
>> Die Funktion von SQL Server MVP und Guru Elmar (Boye) ist schon die
>> richtige,
>
> Korrektur:
>
> Ich bin _nicht_ der Autor von ISOWEEK (und auch nix Guru ;-)
Das mit dem Autor ist ja OK, aber bei dem Nicht Guru muss ich doch heftigst
Widerspruch einlegen. ;-))
Mich hat schon mehr als ein Microsofti gefragt, wer den der Superguru sei
oder ob sich dahinter irgendeine Stelle mit mehreren Personen verbirgt, weil
soo schnell und kompetent zu antworten, könne einer alleine doch fast nicht
;-)))) und langjähriger SQL Server MVP wird man ja auch nicht von alleeeene
... ;-))
mfg
Klaus
Elmar Boye
Hallo Klaus,
Klaus Oberdalhoff schrieb:
>>> Die Funktion von SQL Server MVP und Guru Elmar (Boye) ist schon die
>>> richtige,
>>
>> Ich bin _nicht_ der Autor von ISOWEEK (und auch nix Guru ;-)
>
> Das mit dem Autor ist ja OK, aber bei dem Nicht Guru muss ich doch
> heftigst Widerspruch einlegen. ;-))
Was den Guru angeht: Ich kann Dir versichern, dass das letzte Jahr
angereichert war mit vielen und intensiven "00" Meditationen,
das Ergebnis in der Schüssel aber immer das Gleiche ...
> Mich hat schon mehr als ein Microsofti gefragt, wer den der Superguru
> sei oder ob sich dahinter irgendeine Stelle mit mehreren Personen
> verbirgt, weil soo schnell und kompetent zu antworten, könne einer
> alleine doch fast nicht ;-))))
... schizophren bin ich deswegen aber nicht geworden. ;-)
Gruß Elmar
Klaus Oberdalhoff
[...]
Elmar at its best ... <g>
mfg
Klaus
Volkan Senguel
gewünscht doch die nicht korrekte Version selbst implementieren ... auch
wenn es keinen Sinn macht... mal schauen.
Mann sollte halt keine unwissenden Leute Access Datenbanken zusammenklicken
lassen die nicht wirklich einen Plan haben.
Danke nochmals
Volkan
Dieter Noeth
>> Ist es richtig, dass die erste Kalenderwoche immer die erste Woche im
>> Jahr
>> mit mindestens 4 Tagen ist?
>> Wann fängt die Kalenderwoche an? Montag, oder Sonntag?
>
> Das ist weltweit nicht einheitlich geregelt.
Die ISO 8601 regelt das so gut wie weltweit:
http://en.wikipedia.org/wiki/ISO_8601_usage
> In den USA gilt beides NICHT, dort ist immer die Woche die Nr 1 (in
> Worten:eins), die den ersten Januar beinhaltet und der erste Tag der Woche
> ist der Sonntag.
Da ist es wie mit den Gallonen und Inch, eigentlich sind die
abgeschafft, aber bei uns hält sich ja auch kaum jemand an die
yyyy-mm-dd Schreinweise.
Die ultimative Antwot auf Kalender-Fragen findest du unter:
http://www.tondering.dk/claus/calendar.html
Dieter