首页 > 代码库 > SQL Server 2008 R2 几个自定义函数

SQL Server 2008 R2 几个自定义函数

  

 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 END12 --时间转成字符串13 GO14 CREATE FUNCTION [dbo].[JBFS_MS_DATETIME_TO_VARCHAR] (@date datetime,@inputNum int15 )16 RETURNS varchar(50)17 AS18 BEGIN19   declare @strTm varchar(50)20   set @strTm =CONVERT(varchar(50),@date,@inputNum);21   RETURN @strTm;22 END23 --货币转换千分位字符串24 25 CREATE function [dbo].[JBFS_MS_MONEY_TO_VARCHAR](@dec money, @n int)26 returns varchar(32) as27 begin28     declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32)29     if @n!=030     begin31         --四舍五入32        set @str= round(@dec,@n)33        select @left=left(@str,charindex(.,@str)-1),@len=len(@left)-234        while @len>135        begin36            select @left=stuff(@left,@len,0,,), @len=@len-337        end38        select @right=left(stuff(@str,1,charindex(.,@str),‘‘), @n),@len=439        while @len <=len(@right)40        begin41            select @right=stuff(@right,@len,0,,), @len=@len+442        end43        set @end= @left+.+@right44     end45     else46     begin47        set @str= round(@dec,@n)48        select @left=left(@str,charindex(.,@str)-1),@len=len(@left)-249        while @len>150        begin51            select @left=stuff(@left,@len,0,,), @len=@len-352        end53        select @right=left(stuff(@str,1,charindex(.,@str),‘‘), @n),@len=454        while @len <=len(@right)55        begin56            select @right=stuff(@right,@len,0,,), @len=@len+457        end58        set @end= @left59     end60     return @end61 End62 --字符串转换成money63 CREATE function [dbo].[JBFS_MS_VARCHAR_TO_MONEY](@dec varchar(50))64 returns money 65    AS66 BEGIN67     DECLARE @mon MONEY68     SET @mon =cast(@dec as money)69  return @mon70 END

 

SQL Server 2008 R2 几个自定义函数