/*
--返回开始时间和结束时间
@Type 日期类型：（“-”为前一期间，无符号为当期，“+”为后一期间）
例：前一年：-Y，当年：Y,+Y:下一年，前一季：-Q，当季：Q，前一月：-M，当月：M，前一周：-W，当周：W，前一天：-D，当天：D，+D：明天
例1： select dbo.GetDateStartFirstAndLast('-Y','S',getdate(),8), dbo.GetDateStartFirstAndLast('+Y','e',getdate(),8) --返回值为 “去年第一天8点和明年最后一天8点”
例2： select dbo.GetDateStartFirstAndLast('q','e',getdate(),0) --返回值为 “本季度最后一天0点”
*/
DROP FUNCTION GetDateStartFirstAndLast
GO
CREATE FUNCTION GetDateStartFirstAndLast
(
@Type VARCHAR(2) , -- 日期类型： Y/Q/M/D
@DateType VARCHAR(1), --S 开始，E 结束
@date DATE ,
@TIME INT -- 0点或8点
)
RETURNS datetime
AS
BEGIN
DECLARE @return_value datetime
IF @DateType = 'S'
BEGIN
IF RIGHT(@Type,1) = 'Y'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(yy, DATEDIFF(yy,0,@date)-1,0))
IF LEFT(@Type,1) = 'Y' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(yy, DATEDIFF(yy,0,@date), 0))
IF LEFT(@Type,1) = '+' SELECT @return_value =dateadd(hh,+@TIME, DATEADD(yy, DATEDIFF(yy,0,@date)+1,0))
END
IF RIGHT(@Type,1) = 'Q'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(qq, DATEDIFF(qq,0,@date)-1, 0))
IF LEFT(@Type,1) = 'Q' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(qq, DATEDIFF(qq,0,@date), 0))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(qq, DATEDIFF(qq,0,@date)+1, 0))
END
IF RIGHT(@Type,1) = 'M'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(mm, DATEDIFF(mm,0,@date)-1, 0))
IF LEFT(@Type,1) = 'M' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(mm, DATEDIFF(mm,0,@date), 0))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(mm, DATEDIFF(mm,0,@date)+1, 0))
END
IF RIGHT(@Type,1) = 'W'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(WK, DATEDIFF(WK,0,@date)-1, 0))
IF LEFT(@Type,1) = 'W' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(WK, DATEDIFF(WK,0,@date), 0))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(WK, DATEDIFF(WK,0,@date)+1, 0))
END
IF RIGHT(@Type,1) = 'D'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(D, DATEDIFF(D,0,@date)-1, 0))
IF LEFT(@Type,1) = 'D' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(D, DATEDIFF(D,0,@date), 0))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,DATEADD(D, DATEDIFF(D,0,@date)+1, 0))
END
END
IF @DateType = 'E'
BEGIN
IF RIGHT(@Type,1) = 'Y'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@date), 0)))
IF LEFT(@Type,1) = 'Y' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@date)+1, 0)))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@date)+2, 0)))
END
IF RIGHT(@Type,1) = 'Q'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@date), 0)))
IF LEFT(@Type,1) = 'Q' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@date)+1, 0)))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@date)+2, 0)))
END
IF RIGHT(@Type,1) = 'M'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@date), 0)))
IF LEFT(@Type,1) = 'M' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@date)+1, 0)))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@date)+2, 0)))
END
IF RIGHT(@Type,1) = 'W'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(WK, DATEDIFF(WK,0,@date), 0)))
IF LEFT(@Type,1) = 'W' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(WK, DATEDIFF(WK,0,@date)+1, 0)))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(WK, DATEDIFF(WK,0,@date)+2, 0)))
END
IF RIGHT(@Type,1) = 'D'
BEGIN
IF LEFT(@Type,1) = '-' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(D, DATEDIFF(D,0,@date), 0)))
IF LEFT(@Type,1) = 'D' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(D, DATEDIFF(D,0,@date)+1, 0)))
IF LEFT(@Type,1) = '+' SELECT @return_value = dateadd(hh,+@TIME,dateadd(ms,-3,DATEADD(D, DATEDIFF(D,0,@date)+2, 0)))
END
END
RETURN @return_value
END
评论10