Datepart : First week of 2005

Hello all,
I'm facing a problem with the datepart function :
If i run those three queries :
select datepart(ww,'2004-12-31 00:00:00.000')
select datepart(ww,'2005-01-01 00:00:00.000')
select datepart(ww,'2005-01-02 00:00:00.000')
The system gives me :
53
01
02
It means that week 01 is only for one day (!?!). But the calendar shows the
first week as starting on 01/02/2004, so what is correct ? How to fix it ?
Thanks for any help
William
Sunday is the start of the first week of the year, since it's no longer in
2004; since Sunday is day 7 (according to SQL Server), and Monday is day 1,
the Monday (the 2nd) is a new week...
You can change that behavior by changing the DATEFIRST value (SET DATEFIRST)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"William Boulet" <William Boulet@.discussions.microsoft.com> wrote in message
news:247962C3-619C-464A-B4EA-DA2A29CDDC8A@.microsoft.com...
> Hello all,
> I'm facing a problem with the datepart function :
> If i run those three queries :
> select datepart(ww,'2004-12-31 00:00:00.000')
> select datepart(ww,'2005-01-01 00:00:00.000')
> select datepart(ww,'2005-01-02 00:00:00.000')
> The system gives me :
> 53
> 01
> 02
> It means that week 01 is only for one day (!?!). But the calendar shows
the
> first week as starting on 01/02/2004, so what is correct ? How to fix it
?
> Thanks for any help
> William
|||There isn't a single right answer. Calendar weeks and years just don't fit
together. However, SQL Server's DATEPART doesn't follow the ISO Standard
week numbering convention so you may find it doesn't give the same week
numbers you get from other sources. Search for ISOWEEK in Books Online for
an example of how to calculate the ISO week number.
David Portas
SQL Server MVP
|||Straight from Books Online:
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1
of any year defines the starting number for the week datepart, for example:
DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
The weekday (dw) datepart returns a number that corresponds to the day of
the week, for example: Sunday = 1, Saturday = 7. The number produced by the
weekday datepart depends on the value set by SET DATEFIRST, which sets the
first day of the week.
Books Online gives an ISOWeek function as an example of a User-defined
function.
A. Scalar-valued user-defined function that calculates the ISO week
In this example, a user-defined function, ISOweek, takes a date argument and
calculates the ISO week number. For this function to calculate properly, SET
DATEFIRST 1 must be invoked before the function is called.
CREATE FUNCTION ISOweek (@.DATE datetime)
RETURNS int
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)
SET @.ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@.DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@.DATE) AS CHAR(2)))+1
--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
Here is the function call. Notice that DATEFIRST is set to 1.
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
Here is the result set.
ISO Week
51
Good Luck.
Sincerely,
Anthony Thomas
"William Boulet" wrote:

> Hello all,
> I'm facing a problem with the datepart function :
> If i run those three queries :
> select datepart(ww,'2004-12-31 00:00:00.000')
> select datepart(ww,'2005-01-01 00:00:00.000')
> select datepart(ww,'2005-01-02 00:00:00.000')
> The system gives me :
> 53
> 01
> 02
> It means that week 01 is only for one day (!?!). But the calendar shows the
> first week as starting on 01/02/2004, so what is correct ? How to fix it ?
> Thanks for any help
> William
|||As you learn more about this subject, be aware that there are differnent
definitions of this... I think the Europeans have a different standard for
this than US...So you might get caught up in that difference as well... ( I
think it triggers on the number of days in the first week which determines
whether it is counted on not - I think that is what differs.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"William Boulet" <William Boulet@.discussions.microsoft.com> wrote in message
news:247962C3-619C-464A-B4EA-DA2A29CDDC8A@.microsoft.com...
> Hello all,
> I'm facing a problem with the datepart function :
> If i run those three queries :
> select datepart(ww,'2004-12-31 00:00:00.000')
> select datepart(ww,'2005-01-01 00:00:00.000')
> select datepart(ww,'2005-01-02 00:00:00.000')
> The system gives me :
> 53
> 01
> 02
> It means that week 01 is only for one day (!?!). But the calendar shows
the
> first week as starting on 01/02/2004, so what is correct ? How to fix it
?
> Thanks for any help
> William