sqlserver function

-- Batch submitted through debugger: SQLQuery9.sql|15|0|C:\Users\Administrator\AppData\Local\Temp\~vs3AA0.sql
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:    <Description, ,>
-- =============================================
ALTER FUNCTION to_char 
(
    -- Add the parameters for the function here
    --<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
    @rule nvarchar(50)='',@datetime datetime
)
RETURNS nvarchar(50)
AS
BEGIN
declare @v_rule nvarchar(50)
declare @v_year nvarchar(4)
declare @v_month nvarchar(2)
declare @v_day nvarchar(2)
declare @v_hour nvarchar(2)
declare @v_min nvarchar(2)
declare @v_sec nvarchar(2)

if @datetime is null
begin
    select @datetime=sysdatetime()
end

select @v_rule=Lower(@rule)
select @v_year=DATEPART(YEAR,@datetime)
select @v_month=DATEPART(month,@datetime)
select @v_day=DATEPART(DAY,@datetime)
select @v_hour=DATEPART(HOUR,@datetime)
select @v_min=DATEPART(MINUTE,@datetime)
select @v_sec=DATEPART(SECOND,@datetime)
--'yyyy-mm-dd'
declare @v_index int

--解析年份规则
select @v_index=CHARINDEX('yy',@v_rule,1)
if @v_index <> 0
begin
    select @v_index=CHARINDEX('yyyy',@v_rule,1)
    if @v_index = 0
    begin
        select @v_rule=REPLACE(@v_rule,'yy','year');
        select @v_year=SUBSTRING(@v_year,3,2);--两位数表示'-yy-'
    end
    else
    begin
        select @v_rule=REPLACE(@v_rule,'yyyy','year');--'yyyy-'
    end
end

--解析月份规则
select @v_index=CHARINDEX('m',@v_rule,1)
if @v_index <> 0 and SUBSTRING(@v_rule,@v_index,2)<>'mi'--判断非'-mi-'
begin
    if CHARINDEX('mm',@v_rule,1)=0--一位数表示'-m-'
    begin
        select @v_index=CHARINDEX('m',@v_rule,1)
        --select @v_rule=REPLACE(@v_rule,'m','month');--这里不能替换所有的m,因为m可能会替换掉后面的mi中的m
        set @v_rule=substring(@v_rule,1,@v_index-1)+'month'+substring(@v_rule,@v_index+1,len(@v_rule)-@v_index)
    end
    else
    begin
        select @v_rule=REPLACE(@v_rule,'mm','month');--'-mm-'
        select @v_month=(case LEN(@v_month) when 1 then '0'+@v_month else @v_month end)
    end
end

--解析日份规则
if CHARINDEX('d',@v_rule)<>0
begin
    if CHARINDEX('dd',@v_rule)=0
    begin
        set @v_rule=REPLACE(@v_rule,'d','day')
        select @v_day=case LEN(@v_day) when 1 then '0'+@v_day else @v_day end
    end
    else
    begin
        select @v_rule=replace(@v_rule,'dd','day')
    end
end

--解析时分秒
set @v_index = charindex('h',@v_rule,1)
if @v_index>4 and substring(@v_rule,@v_index-4,5)='month'
begin
set @v_index = charindex('h',@v_rule,@v_index+1)
if @v_index<>0
    if charindex('hh',@v_rule,1)<>0
    begin
        set @v_rule=replace(@v_rule,'hh','hour')
        set @v_hour= (case len(@v_hour) when 1 then '0'+@v_hour else @v_hour end)
    end
    else
        set @v_rule=substring(@v_rule,1,@v_index-1)+'hour'+substring(@v_rule,@v_index+1,len(@v_rule)-@v_index)
end

if charindex('mi',@v_rule,1)<>0
    set @v_rule=replace(@v_rule,'mi','minute')

if charindex('s',@v_rule,1)<>0
    if charindex('ss',@v_rule,1)<>0
    begin
        set @v_rule=replace(@v_rule,'ss','second')
        set @v_sec=(case len(@v_sec) when 1 then '0'+@v_sec else @v_sec end)
    end
    else
        set @v_rule=replace(@v_rule,'s','second')

set @v_rule=REPLACE(@v_rule,'year',@v_year)
set @v_rule=REPLACE(@v_rule,'month',@v_month)
set @v_rule=REPLACE(@v_rule,'day',@v_day)
set @v_rule=REPLACE(@v_rule,'hour',@v_hour)
set @v_rule=REPLACE(@v_rule,'minute',@v_min)
set @v_rule=REPLACE(@v_rule,'second',@v_sec)

    -- Declare the return variable here
    --DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    --SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN @v_rule
END