SQL Server 2008 R2 幾個自訂函數

標籤：style   blog   color   io   ar   div   cti   log   sp   

  

 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!=‘0‘30     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 幾個自訂函數