Week Number
-
I have read some of the posts on the SET DATEFIRST option but cannot work out how I integrate any of the solutions into my script.
BEGIN
DECLARE @renWeek int
SET @renWeek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
IF (@renWeek=0)
SET @renWeek=dbo.renWeek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @renWeek=1
RETURN(@renWeek)
END
The problem with the above script is that if I specify the date of 29/01/2006 it returns week number 5 but the date actually belongs in week number 4. Can anyone explain how the script can be amended to calculate the week number based upon Monday being the first day of the week.
Thanks
-
Why do you consider 2006-01-29 as week 4 ? Your week 1 is start from which date ?
-
Hi,
Our 1st week of the year started on 2nd Jan until 8th Jan which is why 29th Jan is our week 4.
Thanks
-
let's just save time ....
In our company, Week no 1 is the week wich contains January 4-th.
I've implemented these UDFs :
Maybe this gets you on track
use master
use master
if exists (SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=N'FUNCTION'
AND ROUTINE_SCHEMA=N'dbo'
AND ROUTINE_NAME = N'fn_ALZDBA_convert_date2WeekRangeLocal' )
drop function [dbo].[fn_ALZDBA_convert_date2WeekRangeLocal]
GO
CREATE FUNCTION dbo.fn_ALZDBA_convert_date2WeekRangeLocal (@RefDate datetime, @FirstLast char(1) = 'F')
RETURNS datetime
AS
BEGIN
-- ALZDBA 2003/12/30
-- 4 january is week 1 !
-- Localy the first day of the week is Monday !
-- Datefirst for our servers is 7 !!!!
--
DECLARE @return_date as datetime
declare @Firstdate datetime
declare @WrkDate datetime
declare @refDays int
select @WrkDate = cast(convert(char(10), @RefDate, 121) as datetime)
select @refDays = (datepart(dw,@WrkDate) - 1) * (-1)
select @Firstdate = dateadd( dd, @refDays, @WrkDate)
if @@DATEFIRST = 7 -- Localy add 1 day
begin
select @Firstdate = dateadd(dd, 1,@Firstdate)
end
if @RefDate < @Firstdate
begin
select @Firstdate = dateadd(dd, (-7) ,@Firstdate)
end
if @FirstLast = 'F'
begin
set @return_date = @Firstdate
end
else
begin
select @return_date = dateadd(ms,-2,dateadd(dd,7,@Firstdate))
end
RETURN (@return_date)
END
go
GRANT EXECUTE ON [dbo].[fn_ALZDBA_convert_date2WeekRangeLocal] TO [public]
GO
if exists (SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=N'FUNCTION'
AND ROUTINE_SCHEMA=N'dbo'
AND ROUTINE_NAME = N'fn_ALZDBA_convert_date2WeekLocal' )
drop function [dbo].[fn_ALZDBA_convert_date2WeekLocal]
GO
CREATE FUNCTION dbo.fn_ALZDBA_convert_date2WeekLocal (@RefDate datetime)
RETURNS integer
AS
BEGIN
-- JOBI 2003/12/30
-- Bij ALZ valt 4 januari altijd in week 1 !
declare @ALZRefDayFirstWeek as datetime
declare @ALZRefDayLastWeek as datetime
declare @WrkDay as datetime
declare @WrkWeek1 as int
declare @WrkFirstALZWeekDay1 as datetime
declare @WrkFirstALZWeekDay7 as datetime
declare @WrkCurrentALZWeekDay1 as datetime
declare @WrkCurrentALZWeekDay7 as datetime
declare @WrkWeekYear as int
declare @ALZWeekNr as int
declare @ALZWeekOffset as int
-- bepalen dag1 van ALZweek 1 en dag1 van de @RefDate-ALZWeek
select @ALZRefDayFirstWeek = convert(datetime, cast(year(@RefDate) as char(4)) + '/01/04',121)
, @ALZRefDayLastWeek = convert(datetime, cast(year(@RefDate) as char(4)) + '/12/31',121)
Select @WrkWeek1 = datepart(wk,@ALZRefDayFirstWeek)
, @WrkFirstALZWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayFirstWeek,'F')
, @WrkFirstALZWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayFirstWeek,'L')
, @WrkCurrentALZWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@RefDate,'F')
, @WrkCurrentALZWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@RefDate,'L')
-- Bij MS week1 contains 01/01
-- 4 january is week 1 !
-- Localy the first day of the week is Monday !
Select @WrkWeekYear = datepart(yyyy,@WrkCurrentALZWeekDay1)
, @WrkDay = @WrkCurrentALZWeekDay1
IF datepart(yyyy,@WrkCurrentALZWeekDay1) < datepart(yyyy,@WrkCurrentALZWeekDay7)
begin
if @RefDate between @WrkFirstALZWeekDay1 and @WrkFirstALZWeekDay7
begin
select @ALZWeekOffset = case when datepart(dd,@WrkFirstALZWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZWeekOffset = case datepart(wk,@WrkFirstALZWeekDay1) - @ALZWeekOffset
when 0
then 0
else @ALZWeekOffset end
if @ALZWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZWeekDay7)
, @WrkDay = convert(datetime, cast(datepart(yyyy,@WrkFirstALZWeekDay7) as char(4)) + '/01/01',121)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZWeekDay1)
, @ALZWeekOffset = 1
end
end
else
begin
declare @WrkLastALZWeekDay1 as datetime
declare @WrkLastALZWeekDay7 as datetime
Select @WrkLastALZWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayLastWeek,'F')
, @WrkLastALZWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayLastWeek,'L')
if @RefDate between @WrkLastALZWeekDay1 and @WrkLastALZWeekDay7
begin
if datepart(dd,@WrkLastALZWeekDay7) < 4
begin
select @ALZWeekOffset = case when datepart(dd,@WrkFirstALZWeekDay7) between 4 and 7
then 0
else 1
end
-- week 00 not allowed !
select @ALZWeekOffset = case datepart(wk,@WrkFirstALZWeekDay1) - @ALZWeekOffset
when 0
then 0
else @ALZWeekOffset end
if @ALZWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZWeekDay7)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZWeekDay1)
end
end
else
begin
select @ALZWeekOffset = case when datepart(dd,@WrkLastALZWeekDay7) between 4 and 7
then 0
else 1
end
-- week 00 not allowed !
select @ALZWeekOffset = case datepart(wk,@WrkLastALZWeekDay1) - @ALZWeekOffset
when 0
then 0
else @ALZWeekOffset end
if @ALZWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZWeekDay7)
, @WrkDay = convert(datetime, cast(datepart(yyyy,@RefDate) as char(4)) + '/01/01',121)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZWeekDay1)
--, @ALZWeekOffset = 1
end
end
end
else
begin
declare @WrkFirstALZWeekDay1LastYear as datetime
declare @WrkFirstALZWeekDay7LastYear as datetime
select @ALZRefDayFirstWeek = convert(datetime, cast((datepart(yyyy,@RefDate) - 1) as char(4)) + '/01/04',121)
select @WrkFirstALZWeekDay1LastYear = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayFirstWeek,'F')
, @WrkFirstALZWeekDay7LastYear = dbo.fn_ALZDBA_convert_date2WeekRangeLocal(@ALZRefDayFirstWeek,'L')
select @ALZWeekOffset = case when datepart(dd,@WrkFirstALZWeekDay7LastYear) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZWeekOffset = case datepart(wk,@WrkFirstALZWeekDay1LastYear) - @ALZWeekOffset
when 0
then 0
else @ALZWeekOffset end
end
end
end
ELSE
begin
select @ALZWeekOffset = case when datepart(dd,@WrkFirstALZWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZWeekOffset = case datepart(wk,@WrkFirstALZWeekDay1) - @ALZWeekOffset
when 0
then 0
else @ALZWeekOffset end
end
Select @ALZWeekNr = (@WrkWeekYear * 100 ) + datepart(wk,@WrkDay) - @ALZWeekOffset
RETURN (@ALZWeekNr)
END
go
GRANT EXECUTE ON [dbo].[fn_ALZDBA_convert_date2WeekLocal ] TO [public]
GO
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This returns 4 for '29 Jan 2006'
---------------------
declare @date datetime
select @date = '29 Jan 2006'
SET DATEFIRST 1
BEGIN
DECLARE @renWeek int
SET @renWeek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
IF (@renWeek=0)
SET @renWeek=dbo.renWeek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @renWeek=1
-- RETURN(@renWeek)
select @renWeek
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply