MSSQL: 函数

--网摘了一些在SQL SERVER 2008 环境下的自定义函数 

creATE FUNCTION B1CFLAddMonthsAndDays(@dtIn DATETIME, @siMonths SMALLINT, @siDays SMALLINT)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		RETURN dbo.B1CFLSetTimeToZero(DATEADD(dd, @siDays, DATEADD(mm, @siMonths, @dtIn)))
	END
go


creATE FUNCTION B1CFLConsiderHoliday(@dtIn DATETIME, @nvHldCode NVARCHAR(20))
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		IF @nvHldCode IS NULL
		BEGIN
			RETURN @dtIn
		END
 
		DECLARE @siWndFrm SMALLINT -- Although OHLD.WndFrm is CHAR(1) !!!
		DECLARE @siWndTo SMALLINT -- Although OHLD.WndTo is CHAR(1) !!!
		DECLARE @cIsCurYear CHAR(1)
		DECLARE @cIgnrWnd CHAR(1)
		SELECT @siWndFrm = CAST(WndFrm AS SMALLINT), @siWndTo = CAST(WndTo AS SMALLINT), @cIsCurYear = IsCurYear, @cIgnrWnd = IgnrWnd FROM OHLD WHERE HldCode = @nvHldCode
 
		DECLARE @siWeekDay SMALLINT
		DECLARE @dtLast DATETIME
		SET @dtLast = @dtIn
		WHILE @dtLast IS NOT NULL
		BEGIN
			IF @cIsCurYear = 'Y'
			BEGIN
				SELECT @dtLast = MAX(EndDate) FROM HLD1 WHERE HldCode = @nvHldCode AND @dtIn >= StrDate AND @dtIn <= EndDate
			END
			ELSE
			BEGIN
				SELECT @dtLast = MAX(dbo.B1CFLCreateDate(YEAR(@dtIn), MONTH(EndDate), DAY(EndDate))) FROM HLD1
				WHERE HldCode = @nvHldCode AND
				@dtIn >= dbo.B1CFLCreateDate(YEAR(@dtIn), MONTH(StrDate), DAY(StrDate)) AND
				@dtIn <= dbo.B1CFLCreateDate(YEAR(@dtIn), MONTH(EndDate), DAY(EndDate))
			END
			IF @dtLast IS NOT NULL
			BEGIN
				SET @dtIn = DATEADD(dd, DATEDIFF(dd, @dtIn, @dtLast) + 1, @dtIn)
				CONTINUE
			END
 
			IF @cIgnrWnd = 'N'
			BEGIN
				SET @siWeekDay = dbo.B1CFLDayOfWeek(@dtIn)
				IF @siWndFrm <= @siWndTo
				BEGIN
					IF @siWeekDay >= @siWndFrm AND @siWeekDay <= @siWndTo
					BEGIN
						SET @dtIn = DATEADD(dd, @siWndTo - @siWeekDay + 1, @dtIn)
						SET @dtLast = @dtIn
					END
				END
				ELSE
				BEGIN
					IF @siWeekDay >= @siWndFrm
					BEGIN
						SET @dtIn = DATEADD(dd, 8 + @siWndTo - @siWeekDay, @dtIn)
						SET @dtLast = @dtIn
					END
					ELSE
					BEGIN
						IF @siWeekDay <= @siWndTo
						BEGIN
							SET @dtIn = DATEADD(dd, 1 + @siWndTo - @siWeekDay, @dtIn)
							SET @dtLast = @dtIn
						END
					END
				END
			END
		END
 
		RETURN dbo.B1CFLSetTimeToZero(@dtIn)
	END

go


creATE FUNCTION B1CFLCreateDate(@iYear SMALLINT, @iMonth SMALLINT, @iDay SMALLINT)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		RETURN DATEADD(mm, ((@iYear - 1900) * 12) + @iMonth - 1, @iDay - 1)
	END

go

creATE FUNCTION B1CFLMonthEnd(@dtIn DATETIME)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		RETURN dbo.B1CFLSetTimeToZero(DATEADD(ss, -1, DATEADD(mm, DATEDIFF(mm, 0, @dtIn) + 1, 0)))
	END

go
creATE FUNCTION B1CFLMonthHalf(@dtIn DATETIME)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		RETURN dbo.B1CFLSetTimeToZero(DATEADD(dd, DAY(dbo.B1CFLMonthEnd(@dtIn)) / 2, DATEADD(dd, -(DAY(@dtIn) - 1), @dtIn)))
	END


go
creATE FUNCTION B1CFLMonthStart(@dtIn DATETIME)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		RETURN DATEADD(dd, 1, dbo.B1CFLMonthEnd(@dtIn))
	END

go
creATE FUNCTION B1CFLSetTimeToZero(@dtIn DATETIME)
	RETURNS DATETIME
	WITH ENCRYPTION
	AS
	BEGIN
		SET @dtIn = DATEADD(ms, -DATEPART(ms, @dtIn), @dtIn)
		SET @dtIn = DATEADD(ss, -DATEPART(ss, @dtIn), @dtIn)
		SET @dtIn = DATEADD(mi, -DATEPART(mi, @dtIn), @dtIn)
		SET @dtIn = DATEADD(hh, -DATEPART(hh, @dtIn), @dtIn)
		RETURN @dtIn
	END
go


create FUNCTION GetCompanyTime() RETURNS datetime AS BEGin DECLARE @OutDate datetime DECLARE @offset1 numeric(9,4) DECLARE @dst varchar(1) select top 1 @offset1 = offset, @dst = isnull(ActiveDst,0) from OTIZ order by id desc if @offset1 is NULL RETURN(getdate()) if (@dst = 'Y') set @offset1 = @offset1 + 60 set @offset1 = @offset1 /1440 RETURN(getUTCdate() + @offset1) END
 
GO

	CREATE FUNCTION B1CFLDayOfWeek(@dtIn DATETIME)
	RETURNS SMALLINT
	AS
	BEGIN
		RETURN ((((@@DATEFIRST - 1) + DATEPART(dw, @dtIn)) % 7) + 1);
	END
 
GO

CREATE FUNCTION [dbo].[TmSp_ToChar]( 
	@ipt sql_variant
)
RETURNS nvarchar(255)
AS
BEGIN

	DECLARE @ret nvarchar(255);
	SELECT @ret = CAST(@ipt as nvarchar(255));
	RETURN @ret;

END
 
GO

　　函数列表：

B1CFLAddMonthsAndDaysl()

B1CFLConsiderHoliday()
B1CFLCreateDate()
B1CFLDayOfWeek()
B1CFLMonthEnd()
B1CFLMonthHalf()
B1CFLMonthStart()
B1CFLSetTimeToZero()
GetCompanyTime()
TmSp_ToChar()