- CREATE FUNCTION GetDateDuration_Day
- (
- @startdate datetime, --查询起始日期
- @ENDdate datetime , --查询结束日期
- @StartDateFee datetime, --缴费起始日期
- @ENDDateFee datetime --缴费结束日期
- )
- RETURNS decimal(18,3)
- WITH EXECUTE AS CALLER
- AS
- BEGIN
- declare @DurationTime int ;
- declare @rate decimal(18,3);
- IF (@StartDateFee < @ENDDateFee)
- BEGIN
- IF ((@ENDdate <= @StartDateFee) OR (@startdate >= @ENDDateFee))
- BEGIN
- SET @DurationTime = 0;
- END
- IF ((@startdate <= @StartDateFee) AND (@ENDdate <= @ENDDateFee) AND (@StartDateFee <=@ENDdate) )
- BEGIN
- SET @DurationTime = DateDIFf(day, @StartDateFee, @ENDdate)
- END
- IF ((@startdate <= @StartDateFee) AND (@ENDdate >= @ENDDateFee) AND (@StartDateFee <= @ENDDateFee))
- BEGIN
- SET @DurationTime = DateDIFf(day, @StartDateFee, @ENDDateFee)
- END
- IF ((@startdate >= @StartDateFee AND @ENDdate<= @ENDDateFee) AND (@startdate <= @ENDdate))
- BEGIN
- SET @DurationTime = DateDIFf(day, @startdate, @ENDdate)
- END
- IF ((@startdate >= @StartDateFee AND @ENDdate>= @ENDDateFee) AND (@startdate <= @ENDDateFee))
- BEGIN
- SET @DurationTime = DateDIFf(day, @startdate, @ENDDateFee)
- END
- SET @rate = cast(@DurationTime as decimal(18,3))/DateDIFf(day, @StartDateFee, @ENDDateFee);
- --SET @rate = @DurationTime;
- END
- else IF(@StartDateFee >= @ENDDateFee)
- BEGIN
- set @rate = 0;
- END
- return @rate;
- END
于上雷同,下面是月份的计算
- CREATE FUNCTION GetDateDuration_Month
- (
- @startdate datetime, --查询起始日期
- @ENDdate datetime , --查询结束日期
- @StartDateFee datetime, --起始日期
- @ENDDateFee datetime --结束日期
- )
- RETURNS decimal(18,3)
- WITH EXECUTE AS CALLER
- AS
- BEGIN
- declare @DurationTime int ;
- declare @rate decimal(18,3);
- IF (@StartDateFee < @ENDDateFee)
- BEGIN
- IF ((@ENDdate <= @StartDateFee) OR (@startdate >= @ENDDateFee) )
- BEGIN
- SET @DurationTime = 0;
- END
- IF ((@startdate <= @StartDateFee) AND (@ENDdate <= @ENDDateFee) AND (@StartDateFee <=@ENDdate) )
- BEGIN
- SET @DurationTime = DATEDIFF(Month, @StartDateFee, @ENDdate)
- END
- IF ((@startdate <= @StartDateFee) AND (@ENDdate >= @ENDDateFee) AND (@StartDateFee <= @ENDDateFee))
- BEGIN
- SET @DurationTime = DATEDIFF(Month, @StartDateFee, @ENDDateFee)
- END
- IF ((@startdate >= @StartDateFee AND @ENDdate<= @ENDDateFee) AND (@startdate <= @ENDdate))
- BEGIN
- SET @DurationTime = DATEDIFF(Month, @startdate, @ENDdate)
- END
- IF ((@startdate >= @StartDateFee AND @ENDdate>= @ENDDateFee) AND (@startdate <= @ENDDateFee))
- BEGIN
- SET @DurationTime = DATEDIFF(Month, @startdate, @ENDDateFee)
- END
- SET @rate = cast(@DurationTime as decimal(18,3))/DATEDIFF(Month, @StartDateFee, @ENDDateFee);
- END
- else IF(@StartDateFee >= @ENDDateFee)
- BEGIN
- set @rate = 0;
- END
- return @rate;
- END