sql server reporting services: I need to group data in one of my reports by week end date (Saturday). I've
created a custom assembly with code to do this, but am having trouble getting
the assembly to load. So, I've decided to go to the community. Anybody got
any ideas on coding a function to return week end date given any date in? I
realize that the solution could very well be right under my nose. I just need
someone to point me to it.

--
Chris Mackey
Sr. Software Developer

use the custom code, in report properties.
This is some perl code to do the same thing:
var cur_date = new Date(y_end_date);
cur_date=DayAdd(cur_date,-7);
var to_sat=6 - cur_date.getDay(); #day is numbered 1-7 from getDay()
cur_date=DayAdd(cur_date,to_sat); #adds number of days till saturday.

I don't need that in reporting services, cus I pass it in. but you can use
vb functions like
dateAdd, and DatePart. Basically try to do the same thing i did above

Here's one of my functions in custom code I use (Displays the weeknumber,
and date range, given the saturday in)
function displayWeek(Date1)

Dim Date2=DateAdd("d",-6,Date1)
displayWeek="Week: " & DatePart("ww", Date1 ) & " - " & DatePart("m",
Date2 ) & "/" & DatePart("d", Date2 ) & "/" & DatePart("yyyy", Date2 ) & " -
" & DatePart("m", Date1 ) & "/" & DatePart("d", Date1 ) & "/" &
DatePart("yyyy", Date1 )

End function



Here are some other functions I use, they are mostly for displaying dates
function mToNum(dMonth)
mToNum="1"
if dMonth = "JANUARY" Then
mToNum="1"
End if
if dMonth = "FEBRUARY" Then
mToNum="2"
End if
if dMonth = "MARCH" Then
mToNum="3"
End if
if dMonth = "APRIL" Then
mToNum="4"
End if
if dMonth = "MAY" Then
mToNum="5"
End if
if dMonth = "JUNE" Then
mToNum="6"
End if
if dMonth = "JULY" Then
mToNum="7"
End if
if dMonth = "AUGUST" Then
mToNum="8"
End if
if dMonth = "SEPTEMBER" Then
mToNum="9"
End if
if dMonth = "OCTOBER" Then
mToNum="10"
End if
if dMonth = "NOVEMBER" Then
mToNum="11"
End if
if dMonth = "DECEMBER" Then
mToNum="12"
End if
End function

function getLaunchDate(Date1)

getLaunchDate =DatePart("m", Date1)& "/"& DatePart("d", Date1) & "/"
&DatePart("YYYY", Date1)
End function
function displayDate(Dtype, dYear, dMonth, dWeek, dDay)
Dim returnStr

if Dtype = "w" Then
returnStr=mToNum(dMonth)&"-" & dYear &" Wk:" & dWeek

End if
if Dtype = "d" Then
returnStr= mToNum(dMonth) & "-" & dDay & "-" & dYear

End if


displayDate=returnStr


End function



[quoted text, click to view]

I needed this function in SQL Server:

CREATE FUNCTION dbo.tc_SaturdayNext(@DateToday DateTime)

RETURNS DateTime

AS

BEGIN

DECLARE @return DateTime

Select @DateToday = CAST(FLOOR(CAST(@DateToday AS FLOAT))AS DATETIME)

IF DATENAME(WEEKDAY, @DateToday) = 'Sunday'
BEGIN
SELECT @return = DATEADD(d, 6, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Monday'
BEGIN
SELECT @return = DATEADD(d, 5, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Tuesday'
BEGIN
SELECT @return = DATEADD(d, 4, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Wednesday'
BEGIN
SELECT @return = DATEADD(d, 3, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Thursday'
BEGIN
SELECT @return = DATEADD(d, 2, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Friday'
BEGIN
SELECT @return = DATEADD(d, 1, @DateToday)
END

IF DATENAME(WEEKDAY, @DateToday) = 'Saturday'
BEGIN
SELECT @return = DATEADD(d, 0, @DateToday)
END

RETURN @return

END