sqlserver function
时间：2021-07-01 10:21:17
帮助过：3人阅读
-- Batch submitted through debugger: SQLQuery9.sql|15|0|C:\Users\Administrator\AppData\Local\Temp\~vs3AA0.sql
2 -- =============================================
3 -- Author: <Author,,Name>
4 -- Create date: <Create Date, ,>
5 -- Description: <Description, ,>
6 -- =============================================
7 ALTER FUNCTION to_char
8 (
9 -- Add the parameters for the function here
10 --<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
11 @rule nvarchar(
50)
=‘‘,
@datetime datetime
12 )
13 RETURNS nvarchar(
50)
14 AS
15 BEGIN
16 declare @v_rule nvarchar(
50)
17 declare @v_year nvarchar(
4)
18 declare @v_month nvarchar(
2)
19 declare @v_day nvarchar(
2)
20 declare @v_hour nvarchar(
2)
21 declare @v_min nvarchar(
2)
22 declare @v_sec nvarchar(
2)
23
24 if @datetime is null
25 begin
26 select @datetime=sysdatetime()
27 end
28
29 select @v_rule=Lower(
@rule)
30 select @v_year=DATEPART(
YEAR,
@datetime)
31 select @v_month=DATEPART(
month,
@datetime)
32 select @v_day=DATEPART(
DAY,
@datetime)
33 select @v_hour=DATEPART(HOUR,
@datetime)
34 select @v_min=DATEPART(MINUTE,
@datetime)
35 select @v_sec=DATEPART(SECOND,
@datetime)
36 --‘yyyy-mm-dd‘
37 declare @v_index int
38
39 --解析年份规则
40 select @v_index=CHARINDEX(
‘yy‘,
@v_rule,
1)
41 if @v_index <> 0
42 begin
43 select @v_index=CHARINDEX(
‘yyyy‘,
@v_rule,
1)
44 if @v_index = 0
45 begin
46 select @v_rule=REPLACE(
@v_rule,
‘yy‘,
‘year‘);
47 select @v_year=SUBSTRING(
@v_year,
3,
2);
--两位数表示‘-yy-‘
48 end
49 else
50 begin
51 select @v_rule=REPLACE(
@v_rule,
‘yyyy‘,
‘year‘);
--‘yyyy-‘
52 end
53 end
54
55 --解析月份规则
56 select @v_index=CHARINDEX(
‘m‘,
@v_rule,
1)
57 if @v_index <> 0 and SUBSTRING(
@v_rule,
@v_index,
2)
<>‘mi‘--判断非‘-mi-‘
58 begin
59 if CHARINDEX(
‘mm‘,
@v_rule,
1)
=0--一位数表示‘-m-‘
60 begin
61 select @v_index=CHARINDEX(
‘m‘,
@v_rule,
1)
62 --select @v_rule=REPLACE(@v_rule,‘m‘,‘month‘);--这里不能替换所有的m,因为m可能会替换掉后面的mi中的m
63 set @v_rule=substring(
@v_rule,
1,
@v_index-1)
+‘month‘+substring(
@v_rule,
@v_index+1,
len(
@v_rule)
-@v_index)
64 end
65 else
66 begin
67 select @v_rule=REPLACE(
@v_rule,
‘mm‘,
‘month‘);
--‘-mm-‘
68 select @v_month=(
case LEN(
@v_month)
when 1 then ‘0‘+@v_month else @v_month end)
69 end
70 end
71
72 --解析日份规则
73 if CHARINDEX(
‘d‘,
@v_rule)
<>0
74 begin
75 if CHARINDEX(
‘dd‘,
@v_rule)
=0
76 begin
77 set @v_rule=REPLACE(
@v_rule,
‘d‘,
‘day‘)
78 select @v_day=case LEN(
@v_day)
when 1 then ‘0‘+@v_day else @v_day end
79 end
80 else
81 begin
82 select @v_rule=replace(
@v_rule,
‘dd‘,
‘day‘)
83 end
84 end
85
86 --解析时分秒
87 set @v_index = charindex(
‘h‘,
@v_rule,
1)
88 if @v_index>4 and substring(
@v_rule,
@v_index-4,
5)
=‘month‘
89 begin
90 set @v_index = charindex(
‘h‘,
@v_rule,
@v_index+1)
91 if @v_index<>0
92 if charindex(
‘hh‘,
@v_rule,
1)
<>0
93 begin
94 set @v_rule=replace(
@v_rule,
‘hh‘,
‘hour‘)
95 set @v_hour= (
case len(
@v_hour)
when 1 then ‘0‘+@v_hour else @v_hour end)
96 end
97 else
98 set @v_rule=substring(
@v_rule,
1,
@v_index-1)
+‘hour‘+substring(
@v_rule,
@v_index+1,
len(
@v_rule)
-@v_index)
99 end
100
101 if charindex(
‘mi‘,
@v_rule,
1)
<>0
102 set @v_rule=replace(
@v_rule,
‘mi‘,
‘minute‘)
103
104 if charindex(
‘s‘,
@v_rule,
1)
<>0
105 if charindex(
‘ss‘,
@v_rule,
1)
<>0
106 begin
107 set @v_rule=replace(
@v_rule,
‘ss‘,
‘second‘)
108 set @v_sec=(
case len(
@v_sec)
when 1 then ‘0‘+@v_sec else @v_sec end)
109 end
110 else
111 set @v_rule=replace(
@v_rule,
‘s‘,
‘second‘)
112
113 set @v_rule=REPLACE(
@v_rule,
‘year‘,
@v_year)
114 set @v_rule=REPLACE(
@v_rule,
‘month‘,
@v_month)
115 set @v_rule=REPLACE(
@v_rule,
‘day‘,
@v_day)
116 set @v_rule=REPLACE(
@v_rule,
‘hour‘,
@v_hour)
117 set @v_rule=REPLACE(
@v_rule,
‘minute‘,
@v_min)
118 set @v_rule=REPLACE(
@v_rule,
‘second‘,
@v_sec)
119
120 -- Declare the return variable here
121 --DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
122
123 -- Add the T-SQL statements to compute the return value here
124 --SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
125
126 -- Return the result of the function
127 RETURN @v_rule
128 END
sqlserver function
标签：
