sqlserver日记

sqlserver

日期转换成中文

create function dbo.cFN_DateTimeToChinese    
(	
	@date_time datetime
)    
returns nvarchar(20)    
as    
begin    
	declare  @temStr nvarchar(2)    ;
	declare  @connStr nvarchar(20)    ;
	declare @year nvarchar(4);
	declare @month nvarchar(2);
	declare @day nvarchar(2);
	declare @next int;
	declare @max int;

	set @year = DATENAME(YYYY,@date_time) ;
	set @month = DATENAME(MM,@date_time) ;
	set @day = DATENAME(DD,@date_time) ;
	--set @max = LEN(@year) ;
	set @next = 1 ;
	set @connStr = '';
	--年份转换
	while @next <= LEN(@year)
	begin
		 set @temStr= (case when  SUBSTRING(@year,@next,1) = '1' then  N'一'    
				                when SUBSTRING(@year,@next,1) ='2' then N'二'    
				                when SUBSTRING(@year,@next,1) ='3' then N'三'    
				                when SUBSTRING(@year,@next,1) ='4' then N'四'  
				                when SUBSTRING(@year,@next,1) ='5' then N'五'   
				                when SUBSTRING(@year,@next,1) ='6' then N'六'    
				                when SUBSTRING(@year,@next,1) ='7' then N'七'    
				                when SUBSTRING(@year,@next,1) ='8' then N'八'    
				                when SUBSTRING(@year,@next,1) ='9' then N'九'    
				                when SUBSTRING(@year,@next,1) ='0' then N'〇' else N'-' end);
		
		set @connStr = @connStr + @temStr;
		set @next = @next + 1
	end;

	set @connStr = @connStr + N'年' ;

	--月份转换
	set @next = 1 ;
	while @next <= LEN(@month)
	begin
		 set @temStr= (case when  SUBSTRING(@month,@next,1) = '1' then  N'一'    
				                when SUBSTRING(@month,@next,1) ='2' then N'二'    
				                when SUBSTRING(@month,@next,1) ='3' then N'三'    
				                when SUBSTRING(@month,@next,1) ='4' then N'四'  
				                when SUBSTRING(@month,@next,1) ='5' then N'五'   
				                when SUBSTRING(@month,@next,1) ='6' then N'六'    
				                when SUBSTRING(@month,@next,1) ='7' then N'七'    
				                when SUBSTRING(@month,@next,1) ='8' then N'八'    
				                when SUBSTRING(@month,@next,1) ='9' then N'九'    
				                when SUBSTRING(@month,@next,1) ='0' then N'〇' else N'-' end);
		
		set @connStr = @connStr + @temStr;
		set @next = @next + 1
	end;

	set @connStr = @connStr + N'月' ;

	--日期转换
	set @next = 1 ;
	while @next <= LEN(@day)
	begin
		 set @temStr= (case when  SUBSTRING(@day,@next,1) = '1' then  N'一'    
				                when SUBSTRING(@day,@next,1) ='2' then N'二'    
				                when SUBSTRING(@day,@next,1) ='3' then N'三'    
				                when SUBSTRING(@day,@next,1) ='4' then N'四'  
				                when SUBSTRING(@day,@next,1) ='5' then N'五'   
				                when SUBSTRING(@day,@next,1) ='6' then N'六'    
				                when SUBSTRING(@day,@next,1) ='7' then N'七'    
				                when SUBSTRING(@day,@next,1) ='8' then N'八'    
				                when SUBSTRING(@day,@next,1) ='9' then N'九'    
				                when SUBSTRING(@day,@next,1) ='0' then N'〇' else N'-' end);
		
		set @connStr = @connStr + @temStr;
		set @next = @next + 1
	end;
	    set @connStr = @connStr + N'日' ;
	    return @connStr ;
end;

查看对象依赖

   sp_depends object_name ;
   select * from  sys.sql_modules where definition like '%sql shell%';