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'
或者，如果你只想要值而不需要描述信息。
SELECT
DATEADD(yy, -1, CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
UNION
SELECT
DATEADD(yy, -1, GETDATE()) AS TodayLastYear
你可以使用
DATEADD函数并指定yy来减去年份，只需使用负值即可。
我还会分享一组常用的日期函数，你只需要将它们创建为一个表值函数即可：
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
以下是这些函数的标量值：
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
这对我非常有帮助，因为我在报告服务中使用它来进行日期参数设置。您可以简单地创建一个引用此表函数的数据集，然后在RS中的任何日期时间参数中使用它们。
您可以像这样执行整个表值函数：
SELECT * FROM [MyDB].[dbo].[udfCommonDates] (GetDate())
结果如下所示：
对于报告服务人员：
现在我提到了我将这些用于报告服务。现在，报告服务人员可能会想，但这对我有什么帮助，因为我需要一个数据集，而数据集只能基于存储过程或直接表。没问题，创建以下存储过程：
CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
现在你有一个存储过程可以用作数据集...现在在报告服务中添加一个新的数据集：
现在进入报告参数部分：
现在选择数据集dsFunctions（或者你称呼的任何名称），然后从标量函数中选择任何值字段，如：
现在运行报告时它将使用这些标量函数：
