SELECT
DATEADD(yy, -1, CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AS Date_Value,
'First Day of Current Month Of Last Year' AS Date_Type
UNION
SELECT
DATEADD(yy, -1, GETDATE()) AS TodayLastYear, 'Today Last Year'
Or if you just want the values and no description
SELECT
DATEADD(yy, -1, CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
UNION
SELECT
DATEADD(yy, -1, GETDATE()) AS TodayLastYear
You use DATEADD and specify yy to subtract years. Just use a negative value.
I'll also share a set of common date functions I use. Just create this as a table valued function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
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
Here are the scalar valued functions for these:
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
These were really helpful for me because I used this in reporting services for date parameters. You could simply create a dataset referencing this table function and then use these in the parameters for any datetime within RS.
You could execute this entire table-valued function like so:
SELECT * FROM [MyDB].[dbo].[udfCommonDates] (GetDate())
The result is like so
For Reporting Services Folks
Now I mentioned earlier that I use these for reporting services.
Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:
CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:
Now go to the report parameters section of the report:
Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:
Now when you run the report it uses the scalars:
