USE [TCX_32101]
GO

/****** Object:  UserDefinedFunction [dbo].[f_get_format_datetime]    Script Date: 2022/1/17 16:27:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        taozi
-- Create date: 2022年1月17日
-- Description:    获取格式化日期时间
-- =============================================
CREATE FUNCTION [dbo].[f_get_format_datetime] 
(
    @date datetime,
    @format varchar(100)
)
RETURNS varchar(100) 
AS
BEGIN

    --year
    set @format=replace(@format, 'yyyy', cast(year(@date) as char(4)))
    set @format=replace(@format, 'yy', right(cast(year(@date) as char(4)),2))

    --millisecond
    set @format=replace(@format, 'ms', replicate('0',3-len(cast(datepart(ms,@date) as varchar(3)))) + cast(datepart(ms, @date) as varchar(3)))

    --month
    set @format=replace(@format, 'mm', replicate('0',2-len(cast(month(@date) as varchar(2)))) + cast(month(@date) as varchar(2)))
    set @format=replace(@format, 'm', cast(month(@date) as varchar(2)))

    --day
    set @format=replace(@format, 'dd', replicate('0',2-len(cast(day(@date) as varchar(2)))) + cast(day(@date) as varchar(2)))
    set @format=replace(@format, 'd',  cast(day(@date) as varchar(2)))

    --hour
    set @format=replace(@format, 'hh', replicate('0',2-len(cast(datepart(hh,@date) as varchar(2)))) + cast(datepart(hh, @date) as varchar(2)))
    set @format=replace(@format, 'h',  cast(datepart(hh, @date) as varchar(2)))

    --minute
    set @format=replace(@format, 'nn', replicate('0',2-len(cast(datepart(n,@date) as varchar(2)))) + cast(datepart(n, @date) as varchar(2)))
    set @format=replace(@format, 'n', cast(datepart(n, @date) as varchar(2)))

    --second
    set @format=replace(@format, 'ss', replicate('0',2-len(cast(datepart(ss,@date) as varchar(2)))) + cast(datepart(ss, @date) as varchar(2)))
    set @format=replace(@format, 's', cast(datepart(ss, @date) as varchar(2)))

    return @format
END
GO