Does anyone now how I can create a SQL function to return a w
number for any date with the following guidlines?
-W
s begin on a Thursday and end on a Wednesday.
-1st January is always in w
1.
-W
1 for this year would go from 30/12/2004 to 05/01/2005
I have been going round in circles trying to crack this one. I would
be very grateful if anyone has any ideas.Mark
Use DateFirst and DatePart
an Example in T-SQL:
Set DateFirst 4
Declare @.D DateTime Set @.D = '20050101'
Select DatePart(wk, @.d)
To make a function:
-- **********************************
Create Functiondbo.W
Number
(@.D DateTime,
@.FDOW TinyInt) -- The day of w
yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return @.WkNo
End
-- ---
Use it like this:
Select dbo.W
Number('20050106',4)
"Mark Powell" wrote:
> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -W
s begin on a Thursday and end on a Wednesday.
> -1st January is always in w
1.
> -W
1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.
>|||The you tried the built-in DATEPART after setting appropriate SET DATEFIRST?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Powell" <mark@.muddyboots.com> wrote in message
news:f10f176.0503300059.5537cf67@.posting.google.com...
> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -W
s begin on a Thursday and end on a Wednesday.
> -1st January is always in w
1.
> -W
1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.|||Thanks - that's much better than anything I could come up with. The
only problem with this function is that 30/12/04 and 31/12/04 are
returning w
53 and I want them to return w
1.|||That IS counterintuitive, that the last days of 2004 should be considered as
being in the first w
of 2005, But if you want it that way, modify
function so that if value calculates to 53, it returns 1 instead
Alter Functiondbo.W
Number
(@.D DateTime,
@.FDOW TinyInt) -- The day of w
yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return Case @.WkNo When 53
Then 1 Else @.WkNo End
End
"mark@.muddyboots.com" wrote:
> Thanks - that's much better than anything I could come up with. The
> only problem with this function is that 30/12/04 and 31/12/04 are
> returning w
53 and I want them to return w
1.
>|||It doesn't make sense to me either, exept that it keeps the same w
number across the year end. It is how our client wants it, so it's the
way it needs to be done.
The next problem I have found is that the w
number does not return
the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
imagine the +4 in your procedure should be a variable based on the
year, but I am not sure what it does?
Thanks for your help.
Mark|||Mark,
This was much harder than I thought at first, but this is it... Give it a
shot...
ALTER FUNCTION dbo.W
Number
(@.D DateTime, @.FDOW TinyInt)
Returns TinyInt
As
Begin
Declare @.FDOY Smallint, @.Shft Smallint, @.Yr SmallInt
Set @.Yr = Year(@.D)
Set @.FDOY = DatePart(dw, Str(@.Yr,4,0) + '0101') +
(@.@.DateFirst%7) - 1
Set @.Shft = (@.FDOY - @.FDOW + 7) % 7
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d) + @.Shft + 6) / 7
-- Now adjust for last partial w
Return Case When @.WkNo < 53 Then @.WkNo
When @.WkNo > 53 Or @.Shft < 5 Then 1
When @.Shft = 6 Then 53
-- Leap Year Consideration
When DatePart(dy, Str(@.Yr,4,0) + '1231') = 365
Then 1
Else 53 End
End
-- ****************************************
***************
And here is the code to test it...
Declare @.Y SmallInt Set @.Y = 2000
Set NoCOunt On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 4
Declare @.DP TinyInt,@.DF TinyInt,
@.DY SmallInt, @.I TInyInt
Declare @.DTs Table(DT TinyInt)
Set @.I = 0
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
Print 'Year Day Date W
No Day Date W
No '
While @.Y < 2010 Begin
--Set @.D = STR(@.Y, 4,0) + '0101'
Set @.DF = @.@.DateFirst
--Set @.DP = datepart(w
day, @.D)
--Set @.DY = datepart(dy, @.D)
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' + Replace(Str(DT, 2,0),' ', '0')),2)
+
' ' + Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),' ', '0'),8) +
' ' +
Cast(dbo.W
Number(Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),'
', '0'),2), 4) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' + Replace(Str(DT+24, 2,0),' ',
'0')),2) +
' ' + Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24, 2,0),' ', '0'),8)
+ ' ' +
Cast(dbo.W
Number(Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24,
2,0),' ', '0'),2), 4)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
"mark@.muddyboots.com" wrote:
> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w
number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>|||Sorry , error in test script...
Use the following to test UDF In Prev Post
-- ****************************************
********
Set NoCount On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 2
Declare @.Y SmallInt Set @.Y = 2000
-- --
Declare @.I TInyInt Set @.I = 0
Declare @.DTs Table(DT TinyInt)
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
-- ---
Print 'Year Day Date W
No Day Date W
No '
While @.Y < 2010 Begin
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' +
Replace(Str(DT, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.W
Number(Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),2), @.FDOW) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' +
Replace(Str(DT+24, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.W
Number(Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),2), @.FDOW)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
-- ****************************************
********
"mark@.muddyboots.com" wrote:
> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w
number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>
