SQL Server 2008 R2 几个自定义函数

浏览数：30 / 时间：2015年06月12日

 1 --字符串转成时间
 2  GO
 3 CREATE FUNCTION [dbo].[JBFS_MS_VARCHAR_TO_DATETIME] (@strDate varchar(50)
 4 )
 5 RETURNS datetime
 6 AS
 7 BEGIN
 8   declare @tmpTm datetime
 9   set @tmpTm =CONVERT(datetime,@strDate);
10   RETURN @tmpTm;
11 END
12 --时间转成字符串
13 GO
14 CREATE FUNCTION [dbo].[JBFS_MS_DATETIME_TO_VARCHAR] (@date datetime,@inputNum int
15 )
16 RETURNS varchar(50)
17 AS
18 BEGIN
19   declare @strTm varchar(50)
20   set @strTm =CONVERT(varchar(50),@date,@inputNum);
21   RETURN @strTm;
22 END
23 --货币转换千分位字符串
24 
25 CREATE function [dbo].[JBFS_MS_MONEY_TO_VARCHAR](@dec money, @n int)
26 returns varchar(32) as
27 begin
28     declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32)
29     if @n!=‘0‘
30     begin
31         --四舍五入
32        set @str= round(@dec,@n)
33        select @left=left(@str,charindex(‘.‘,@str)-1),@len=len(@left)-2
34        while @len>1
35        begin
36            select @left=stuff(@left,@len,0,‘,‘), @len=@len-3
37        end
38        select @right=left(stuff(@str,1,charindex(‘.‘,@str),‘‘), @n),@len=4
39        while @len <=len(@right)
40        begin
41            select @right=stuff(@right,@len,0,‘,‘), @len=@len+4
42        end
43        set @end= @left+‘.‘+@right
44     end
45     else
46     begin
47        set @str= round(@dec,@n)
48        select @left=left(@str,charindex(‘.‘,@str)-1),@len=len(@left)-2
49        while @len>1
50        begin
51            select @left=stuff(@left,@len,0,‘,‘), @len=@len-3
52        end
53        select @right=left(stuff(@str,1,charindex(‘.‘,@str),‘‘), @n),@len=4
54        while @len <=len(@right)
55        begin
56            select @right=stuff(@right,@len,0,‘,‘), @len=@len+4
57        end
58        set @end= @left
59     end
60     return @end
61 End
62 --字符串转换成money
63 CREATE function [dbo].[JBFS_MS_VARCHAR_TO_MONEY](@dec varchar(50))
64 returns money 
65    AS
66 BEGIN
67     DECLARE @mon MONEY
68     SET @mon =cast(@dec as money)
69  return @mon
70 END