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()

posted @   samrv  阅读(15)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
历史上的今天:
2021-08-31 在SQL Server通过DBLINK执行ORACLE存储过程
点击右上角即可分享
微信分享提示