Date - Start of Week / End of Week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Date - Start of Week / End of Week

Author

Topic

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-02 : 12:36:03

I have found many examples of start of week/end of week but nothing I can seem to convert into SQL SSRS 2008 code or functions or expressions. For example [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307[/url] goes on about it, but either im being daft or I cant use that in SSRS 2008?

I was thinking of creating a cusom function but havent been able to get my head around the problem.

I would like to pass my date in, and it to tell me what date the start of the week is and the end of the week, Sunday being the start of the week in my case.

I have managed to code the expression for start of the week as:

=DateSerial(DatePart("yyyy",Fields!TimeByDay.Value), DatePart("m",Fields!TimeByDay.Value),DatePart(DateInterval.day, CDate(Fields!TimeByDay.Value), FirstDayOfWeek.Sunday))

but this falls over at the start of months and start of the year as it only pulls up the 1st of the month. (end of the week I just added 6 days to it)

Can anyone point me in the direction of custom functions, or any standard functionality in SSRS im missing please?

thanks.

Andrew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 13:32:04

cant you do this from t-sql and assign values to report parameters from parameter tab?

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-04 : 05:32:59

I was under the impression you cannot do full T-sql in SSRS2008? I can't put T-SQL in the report code, or in the expressions so how would I incorporate this into the report? I have no permissions apart from read access to the database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:38:22

quote:
Originally posted by Bigglesuk

I was under the impression you cannot do full T-sql in SSRS2008? I can't put T-SQL in the report code, or in the expressions so how would I incorporate this into the report? I have no permissions apart from read access to the database.

you can do this in t-sql and bring it along with resultset in data tab and then use it in reports

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-04 : 06:35:58

ive managed to make a dataset which has the t-sql "create function dbo.F_START_OF_WEEK" from the link in my first post. Howver it pops up the parameters for Date and Start of week entered, if you fill those in it brings up:

"an error occurred while executing the query. Incorrect syntax near the keyword 'function'. A RETURN statement wita return value cannot be used in this context."

Thats why im confused, I've just used datasets with a select before, nothing like this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:26:46

quote:
Originally posted by Bigglesuk

ive managed to make a dataset which has the t-sql "create function dbo.F_START_OF_WEEK" from the link in my first post. Howver it pops up the parameters for Date and Start of week entered, if you fill those in it brings up:

"an error occurred while executing the query. Incorrect syntax near the keyword 'function'. A RETURN statement wita return value cannot be used in this context."

Thats why im confused, I've just used datasets with a select before, nothing like this.

make it as a procedure rather than function

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-04 : 08:35:42

unfortunatley i dont have write access to the database to make a stored procedure :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:44:52

then create it as an inline query

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-05 : 07:11:51

I managed to code it as a VB.net code

Public Shared Function DateOfFirstDayofWeek(intCurrentDayofWeek As Integer, TheDate As Date) As Date

DateOfFirstDayofWeek = DateAdd("D", intCurrentDayofWeek * (-1) + 1, TheDate)

End Function

and then call this in my expression with:
= Code.DateOfFirstDayofWeek(Weekday(Fields!TimeByDay.Value, vbSunday), Fields!TimeByDay.Value)

andrewhopkinson
Yak Posting Veteran

63 Posts

Posted - 2010-02-05 : 09:50:20

I just worked on this problem this week!

I didn't create a function, but here's a line of code I used:

DateAdd("d",-Weekday(now(),FirstDayofWeek.Tuesday),now())

That gets the monday of the week. If you want to cycle through multiple weeks, you can wrap date adds around it:

DateAdd("ww",-1,DateAdd("d",-Weekday(now(),FirstDayofWeek.Tuesday),now()))

DateAdd("ww",-2,DateAdd("d",-Weekday(now(),FirstDayofWeek.Tuesday),now()))

etc.

dbradish
Starting Member

24 Posts

Posted - 2010-02-22 : 15:55:45

I've created this UDF that I use frequently -- for better or for worse. If you don't have create writes on your server, then wrap it in a SELECT.

Option A:
SELECT PriorYear1
FROM (
SELECT DatePart(yy, GetDate() - 365) as PriorYear1
)x

Option B:
SELECT PriorYear1
FROM fnGetDateRanges(GetDate())

--Function text
CREATE FUNCTION [dbo].[fnGetDateRanges] (@i_Date DateTime)
RETURNS table
AS
RETURN
(
SELECT @i_Date as iDate
, Convert(DateTime,Convert(varchar(8),@i_Date,112),1) as CurrentDate
, Day(@i_Date) as CurrentDay
, DatePart(Week, @i_Date) as CurrentWeek
, Month(@i_Date) as CurrentMonth
, Convert(int,Convert(char(4),Year(@i_Date)) + RIGHT(RTrim('00' + Convert(char(2),Month(@i_Date))),2)) as CurrentYearMonth
, Convert(DateTime,Convert(varchar(8),@i_Date -1,112),1) as PriorDay1
, DatePart(Week, @i_Date - 7) as PriorWeek
, DatePart(mm,dateadd(day,(day(@i_date)-1)*-1,dateadd(month,-1,@i_date))) as PriorMonth
, DatePart(yy, @i_Date - 365) as PriorYear1
, DatePart(yy, @i_Date - 730) as PriorYear2
, DatePart(yy, @i_Date - 1086) as PriorYear3
, DatePart(yy, @i_Date - 1451) as PriorYear4
, DatePart(yy, @i_Date - 1816) as PriorYear5
, DatePart(yy, @i_Date - 2181) as PriorYear6

, CASE WHEN Right(Convert(char(8),@i_Date,112),4) = '0229'
THEN @i_Date - 366
ELSE @i_Date - 365
END as SameDayLastYear

, DATEADD(mm, DATEDIFF(mm, 0, @i_Date), 0) as FirstDayCurrentMonth
, DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0) as FirstDayCurrentWeek
, DATEADD(yy, DATEDIFF(yy, 0, @i_Date), 0) as FirstDayCurrentYear
, dateadd(mm, datediff(mm, 0, dateadd(mm, 1, @i_date)), -1) as LastDayCurrentMonth
, dateadd(ww, datediff(ww, 0, dateadd(ww, 1, @i_date)), -1) as LastDayCurrentWeek
, dateadd(yy, datediff(yy, 0, dateadd(yy, 1, @i_date)), -1) as LastDayCurrentYear
/* This doesn't work on 1/31/YYYY because DateAdd(month,-2,'1/31/08') = 10/31/2007
, Convert(DateTime,Convert(char(8),dateadd(day,(day(@i_date)-1)*-1,dateadd(month,-1,@i_date)),112),1) as FirstDayPriorMonth1

*/
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-1,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-1,@i_Date)))) as FirstDayPriorMonth1
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-2,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-2,@i_Date)))) as FirstDayPriorMonth2
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-3,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-3,@i_Date)))) as FirstDayPriorMonth3
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-4,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-4,@i_Date)))) as FirstDayPriorMonth4
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-5,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-5,@i_Date)))) as FirstDayPriorMonth5
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-6,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-6,@i_Date)))) as FirstDayPriorMonth6
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-7,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-7,@i_Date)))) as FirstDayPriorMonth7
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-8,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-8,@i_Date)))) as FirstDayPriorMonth8
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-9,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-9,@i_Date)))) as FirstDayPriorMonth9
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-10,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-10,@i_Date)))) as FirstDayPriorMonth10
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-11,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-11,@i_Date)))) as FirstDayPriorMonth11
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-12,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-12,@i_Date)))) as FirstDayPriorMonth12
,Convert(Date,Convert(varchar(2),Month(DateAdd(month,-13,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-13,@i_Date)))) as FirstDayPriorMonth13

,Month(DateAdd(month,-1,@i_Date)) as PriorMonth1
,Month(DateAdd(month,-2,@i_Date)) as PriorMonth2
,Month(DateAdd(month,-3,@i_Date)) as PriorMonth3
,Month(DateAdd(month,-4,@i_Date)) as PriorMonth4
,Month(DateAdd(month,-5,@i_Date)) as PriorMonth5
,Month(DateAdd(month,-6,@i_Date)) as PriorMonth6
,Month(DateAdd(month,-7,@i_Date)) as PriorMonth7
,Month(DateAdd(month,-8,@i_Date)) as PriorMonth8
,Month(DateAdd(month,-9,@i_Date)) as PriorMonth9
,Month(DateAdd(month,-10,@i_Date)) as PriorMonth10
,Month(DateAdd(month,-11,@i_Date)) as PriorMonth11
,Month(DateAdd(month,-12,@i_Date)) as PriorMonth12
,Month(DateAdd(month,-13,@i_Date)) as PriorMonth13

,Convert(int,Convert(char(4),Year(DateAdd(month,-1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-1,@i_Date)))),2)) as PriorYearMonth1
,Convert(int,Convert(char(4),Year(DateAdd(month,-2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-2,@i_Date)))),2)) as PriorYearMonth2
,Convert(int,Convert(char(4),Year(DateAdd(month,-3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-3,@i_Date)))),2)) as PriorYearMonth3
,Convert(int,Convert(char(4),Year(DateAdd(month,-4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-4,@i_Date)))),2)) as PriorYearMonth4
,Convert(int,Convert(char(4),Year(DateAdd(month,-5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-5,@i_Date)))),2)) as PriorYearMonth5
,Convert(int,Convert(char(4),Year(DateAdd(month,-6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-6,@i_Date)))),2)) as PriorYearMonth6
,Convert(int,Convert(char(4),Year(DateAdd(month,-7,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-7,@i_Date)))),2)) as PriorYearMonth7
,Convert(int,Convert(char(4),Year(DateAdd(month,-8,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-8,@i_Date)))),2)) as PriorYearMonth8
,Convert(int,Convert(char(4),Year(DateAdd(month,-9,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-9,@i_Date)))),2)) as PriorYearMonth9
,Convert(int,Convert(char(4),Year(DateAdd(month,-10,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-10,@i_Date)))),2)) as PriorYearMonth10
,Convert(int,Convert(char(4),Year(DateAdd(month,-11,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-11,@i_Date)))),2)) as PriorYearMonth11
,Convert(int,Convert(char(4),Year(DateAdd(month,-12,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-12,@i_Date)))),2))as PriorYearMonth12
,Convert(int,Convert(char(4),Year(DateAdd(month,-13,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-13,@i_Date)))),2)) as PriorYearMonth13

,Convert(int,Convert(char(4),Year(DateAdd(month,+1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+1,@i_Date)))),2)) as NextYearMonth1
,Convert(int,Convert(char(4),Year(DateAdd(month,+2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+2,@i_Date)))),2)) as NextYearMonth2
,Convert(int,Convert(char(4),Year(DateAdd(month,+3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+3,@i_Date)))),2)) as NextYearMonth3
,Convert(int,Convert(char(4),Year(DateAdd(month,+4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+4,@i_Date)))),2)) as NextYearMonth4
,Convert(int,Convert(char(4),Year(DateAdd(month,+5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+5,@i_Date)))),2)) as NextYearMonth5
,Convert(int,Convert(char(4),Year(DateAdd(month,+6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+6,@i_Date)))),2)) as NextYearMonth6

, DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0) - 7 as FirstDayPriorWeek1

, DATEADD(yy, DATEDIFF(yy, 366, @i_Date), 0) as FirstDayPriorYear1
, DATEADD(yy, DATEDIFF(yy, 731, @i_Date), 0) as FirstDayPriorYear2
, DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), 0) as FirstDayPriorYear3
, DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), 0) as FirstDayPriorYear4
, DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), 0) as FirstDayPriorYear5
, DATEADD(yy, DATEDIFF(yy, 2191, @i_Date), 0) as FirstDayPriorYear6

, Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@i_date), 0)),112),1) as LastDayPriorMonth1
, Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(ww, DATEDIFF(ww,0,@i_date), 0)),112),1) as LastDayPriorWeek1
-- , Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@i_date), 0)),112),1) as LastDayPriorYear1
,Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,365,@i_date), 0)),112),1) as LastYearLastDayPriorMonth1
, DATEADD(yy, DATEDIFF(yy, 1, @i_Date), -1) as LastDayPriorYear1
, DATEADD(yy, DATEDIFF(yy, 366, @i_Date), -1) as LastDayPriorYear2
, DATEADD(yy, DATEDIFF(yy, 731, @i_Date), -1) as LastDayPriorYear3
, DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), -1) as LastDayPriorYear4
, DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), -1) as LastDayPriorYear5
, DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), -1) as LastDayPriorYear6

, DATEADD(yy, -1, Convert(varchar(25),@i_Date,112)) as PriorYTD1
, DATEADD(yy, -2, Convert(varchar(25),@i_Date,112)) as PriorYTD2
, DATEADD(yy, -3, Convert(varchar(25),@i_Date,112)) as PriorYTD3
, DATEADD(yy, -4, Convert(varchar(25),@i_Date,112)) as PriorYTD4
, DATEADD(yy, -5, Convert(varchar(25),@i_Date,112)) as PriorYTD5
, DATEADD(yy, -6, Convert(varchar(25),@i_Date,112)) as PriorYTD6

, DATEADD(yy, +1, Convert(varchar(25),@i_Date,112)) as NextYTD1
, DATEADD(yy, +2, Convert(varchar(25),@i_Date,112)) as NextYTD2
, DATEADD(yy, +3, Convert(varchar(25),@i_Date,112)) as NextYTD3
, DATEADD(yy, +4, Convert(varchar(25),@i_Date,112)) as NextYTD4
, DATEADD(yy, +5, Convert(varchar(25),@i_Date,112)) as NextYTD5
, DATEADD(yy, +6, Convert(varchar(25),@i_Date,112)) as NextYTD6

, CAST ('1/1/' + Cast(year(@i_Date) - case month(@i_Date) WHEN 1 THEN 1 ELSE 0 END as varchar(10)) as datetime) as FirstDayYearPriorMonth

)

"Challenge me. I will not give up. I will not succumb to any situation. I will not be afraid to be great." ~ D. M. Abrashoff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources