SQL Server 2008 R2 几个自定义函数

  

--字符串转成时间
 GO
CREATE FUNCTION [dbo].[JBFS_MS_VARCHAR_TO_DATETIME] (@strDate varchar(50)
)
RETURNS datetime
AS
BEGIN
  declare @tmpTm datetime
  set @tmpTm =CONVERT(datetime,@strDate);
  RETURN @tmpTm;
END
--时间转成字符串
GO
CREATE FUNCTION [dbo].[JBFS_MS_DATETIME_TO_VARCHAR] (@date datetime,@inputNum int
)
RETURNS varchar(50)
AS
BEGIN
  declare @strTm varchar(50)
  set @strTm =CONVERT(varchar(50),@date,@inputNum);
  RETURN @strTm;
END
--货币转换千分位字符串

CREATE function [dbo].[JBFS_MS_MONEY_TO_VARCHAR](@dec money, @n int)
returns varchar(32) as
begin
    declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32)
    if @n!='0'
    begin
        --四舍五入
       set @str= round(@dec,@n)
       select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
       while @len>1
       begin
           select @left=stuff(@left,@len,0,','), @len=@len-3
       end
       select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
       while @len <=len(@right)
       begin
           select @right=stuff(@right,@len,0,','), @len=@len+4
       end
       set @end= @left+'.'+@right
    end
    else
    begin
       set @str= round(@dec,@n)
       select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
       while @len>1
       begin
           select @left=stuff(@left,@len,0,','), @len=@len-3
       end
       select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
       while @len <=len(@right)
       begin
           select @right=stuff(@right,@len,0,','), @len=@len+4
       end
       set @end= @left
    end
    return @end
End
--字符串转换成money
CREATE function [dbo].[JBFS_MS_VARCHAR_TO_MONEY](@dec varchar(50))
returns money 
   AS
BEGIN
    DECLARE @mon MONEY
    SET @mon =cast(@dec as money)
 return @mon
END