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()
优质生活从拆开始
分类:
SQL / MSSQL
标签:
MSSQL
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2021-08-31 在SQL Server通过DBLINK执行ORACLE存储过程