SSRS Expressions Default Day of Week Parameters

• rka

  SSCommitted

  Points: 1599

  More actions

  July 15, 2010 at 3:51 pm

  #223403

  I am trying to define default data parameters in the report.

  The dates which I want for the parameters are as follows:

  @StartDate = Monday's Date of Current Week

  @EndDate = Sunday's Date of Current Week

  Given that the report can be executed at any day and time within the week and it should automatically pick up Monday's and Sunday's date.

  Any help will be greatly appreciated in giving me a help in writing correct SSRS expression for above

  Thanks

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 15, 2010 at 6:16 pm

  #1193843

  Not sure if this is what you are looking for:

  DECLARE @monday DATETIME

  DECLARE @Sunday DATETIME

  SET @monday = dateadd(wk, datediff(wk, 0, GETDATE()), 0) -- Beginning of this week (Monday)

  SET @Sunday = dateadd(dd, 6, @monday )

  SELECT @monday AS 'Monday',@Sunday AS 'Following Sunday'

  Which returns:

  Monday Following Sunday

  2010-07-12 00:00:00.000 2010-07-18 00:00:00.000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• rka

  SSCommitted

  Points: 1599

  More actions

  July 18, 2010 at 8:25 pm

  #1194688

  Actually I was looking for this solution which I finally managed to figure out

  First Day of Current Week

  Note: Monday is first day of Week

  =DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

  Last Day of Current Week

  Note: Monday is first day of Week

  =DATEADD("d" ,7-DATEPART(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())