I posted this before, you need to make use of a dataset where you can call date functions. See if you can follow this: SQL Server: calculating date ranges it works wonders. Specifically look at the section that reads For Reporting Services Folks.
For you it will be really simple, if they select 11/27/2011 and you want to return 11/1/2011 you just call the dataset which in turns calls the function month_start. It is all well documented in that thread and used extensively.
The jist of it is you need this function in RS:
CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
year_end datetime,
tomorrow_noon datetime,
today_noon datetime,
date_only datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_year_end(@date) AS year_end,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,
dbo.get_date_only(@date) AS DateOnly
RETURN
END
Then you need the scalar valued functions for each one:
CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
END
GO
CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
GO
CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
GO
CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
GO
CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
GO
CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
GO
CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
GO
CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
GO
CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
GO
CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
GO
CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
GO
CREATE FUNCTION [dbo].[get_year_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
RETURN DATEADD(year, DATEDIFF(year, 0, GetDate())+1, 0)-1
END
GO
CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
GO
CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
GO
CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
GO
Now the hard work is done for reporting services remember a dataset can be a stored procedure or a direct table. So you would simply create a stored procedure:
CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
And use this stored procedure as a dataset. So now you have access to all of these nice date functions. So you can now go to your parameters section and call these functions like so:
