MSSQL: 函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163

--网摘了一些在SQL SERVER 2008 环境下的自定义函数 <br><br>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()