业务单位所有的统计按月份按季度按年度,都不是按自然月份。是按照上个月25号到这个月25号,所以我有很多地方需要做的查询以及报表用到这样的函数 group by month(进厂日期),group by year(出厂日期)之类的我想自己写日期函数,代替数据库的year和month函数。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MyMonth](@MyDate datetime)
returns char
as
BEGIN
DECLARE @intYear AS int
DECLARE @intMonth As int
DECLARE @intDay As int
DECLARE @S AS char
If (@intDay > 25)
BEGIN
set @intMonth = @intMonth + 1
If (@intMonth > 12)
BEGIN
set @intYear = @intYear + 1
set @intMonth = 1
End
End
set @S = @intYear & '-' &@intMonth & '月'
return @S
END
函数生成成功,但是测试时:select Mymonth(getdate())'Mymonth' 不是可以识别的 内置函数名称。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MyMonth](@MyDate datetime)
returns char
as
BEGIN
DECLARE @intYear AS int
DECLARE @intMonth As int
DECLARE @intDay As int
DECLARE @S AS char
If (@intDay > 25)
BEGIN
set @intMonth = @intMonth + 1
If (@intMonth > 12)
BEGIN
set @intYear = @intYear + 1
set @intMonth = 1
End
End
set @S = @intYear & '-' &@intMonth & '月'
return @S
END
函数生成成功,但是测试时:select Mymonth(getdate())'Mymonth' 不是可以识别的 内置函数名称。
select dbo.Mymonth(getdate())
returns varchar(20)
as
BEGIN
DECLARE @intYear AS int
DECLARE @intMonth As int
DECLARE @intDay As int
DECLARE @S AS varchar(20)
select @intYear=year(@MyDate),@intMonth=month(@MyDate),@intDay=day(@MyDate)
If (@intDay > 25)
BEGIN
set @intMonth = @intMonth + 1
If (@intMonth > 12)
BEGIN
set @intYear = @intYear + 1
set @intMonth = 1
End
End
set @S = ltrim(@intYear) + '-' +ltrim(@intMonth) + '月'
return @S
END
go
select [dbo].[MyMonth](getdate())
/*--------------------
2009-5月(1 行受影响)
*/
我现在有很多类似下面这样的程序:
SELECT RepairFactory,ModelName,
[1月进厂数]=sum(case when year(ActualEnterDate)=@Inyear and month(ActualEnterDate)=1 then 1 else 0 end),
[1月交付部队数]=sum(case when year(LeaveFactoryDate)=@Inyear and month(LeaveFactoryDate)=1 then 1 else 0 end),
[月底在厂数]= sum(case when ActualEnterDate is not null and LeaveFactoryDate is null then 1 else 0 end),
[已交付部队未支付金额数量]= sum(case when LeaveFactoryDate is not null and AccountYear is null then 1 else 0 end),
[未修竣数]=sum(case when DeliverDate is null then 1 else 0 end) FROM Produce_PlaneRepair"如果想快速更改这些程序,我是不是应该自己写一个year 函数和一个month函数。
我写了2个函数,一个自定义年,一个自定义月份。create FUNCTION [dbo].[theYear](@MyDate datetime)
returns int
as
BEGIN
DECLARE @intYear AS int
DECLARE @intMonth As int
DECLARE @intDay As int
select @intYear=year(@MyDate),@intMonth=month(@MyDate),@intDay=day(@MyDate)
If (@intMonth=12 and @intDay>25)
BEGIN
set @intYear = @intYear + 1
End
return @intYear
END CREATE FUNCTION [dbo].[theMonth](@MyDate datetime)
returns int
as
BEGIN
DECLARE @intYear AS int
DECLARE @intMonth As int
DECLARE @intDay As int
select @intYear=year(@MyDate),@intMonth=month(@MyDate),@intDay=day(@MyDate)
If (@intDay > 25)
BEGIN
set @intMonth = @intMonth + 1
If (@intMonth > 12)
BEGIN
set @intYear = @intYear + 1
set @intMonth = 1
End
End
return @intMonth
END 然后将这些内部函数year和month换成我自己的函数SELECT RepairFactory,ModelName,
[1月进厂数]=sum(case when dbo.theYear(ActualEnterDate)=@Inyear and dbo.theMonth(ActualEnterDate)=1 then 1 else 0 end),
[1月交付部队数]=sum(case when dbo.theYear(LeaveFactoryDate)=@Inyear and dbo.theMonth(LeaveFactoryDate)=1 then 1 else 0 end),
select dbo.Mymonth(getdate())
跟函数内容没有关系