SqlServer_小工具_时间格式化

最新推荐文章于 2024-08-25 15:15:05 发布
转载最新推荐文章于 2024-08-25 15:15:05 发布 · 48 阅读
0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/Thancoo/p/datetimeformat.html
文章标签：
#数据库
本文介绍了一个SQL Server存储过程和函数，用于将日期时间格式化为指定格式的字符串。通过使用临时表和游标来替换格式字符串中的占位符，实现灵活的日期时间格式输出。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
=======================================存储过程================================================
GO
CREATE PROC sp_datetimeformat(@dt DATETIME,@format NVARCHAR(200),@output NVARCHAR(200) OUTPUT)
AS
BEGIN
    SELECT @output=@format;
    CREATE TABLE #tmm(id INT IDENTITY(1,1) PRIMARY KEY,k NVARCHAR(200) COLLATE Chinese_PRC_CS_AS,v NVARCHAR(200) COLLATE Chinese_PRC_CS_AS);
    INSERT INTO #tmm
    VALUES  
        ( N'yyyy',CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200))),
        ( N'yyy',SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),2,3)),
        ( N'yy',SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),3,2)),
        ( N'y',SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),4,1)),
        ( N'MM',dbo.PaddingLeft(CAST(DATEPART(MONTH,@dt) AS NVARCHAR(200)),'0',2)),
        ( N'M',CAST(DATEPART(MONTH,@dt) AS NVARCHAR(200))),
        ( N'dd',dbo.PaddingLeft(CAST(DATEPART(DAY,@dt) AS NVARCHAR(200)),'0',2)),
        ( N'd',CAST(DATEPART(DAY,@dt) AS NVARCHAR(200))),
        ( N'hh',dbo.PaddingLeft(CAST((CASE WHEN DATEPART(HOUR,@dt)<>12 THEN DATEPART(HOUR,@dt)%12 ELSE 12 END) AS NVARCHAR(200)),'0',2)),
        ( N'h',CAST(CASE WHEN DATEPART(HOUR,@dt)<>12 THEN DATEPART(HOUR,@dt)%12 ELSE 12 END AS NVARCHAR(200))),
        ( N'HH',dbo.PaddingLeft(CAST(DATEPART(HOUR,@dt) AS NVARCHAR(200)),'0',2)),
        ( N'H',CAST(DATEPART(HOUR,@dt) AS NVARCHAR(200))),
        ( N'mm',dbo.PaddingLeft(CAST(DATEPART(MINUTE,@dt) AS NVARCHAR(200)),'0',2)),
        ( N'm',CAST(DATEPART(MINUTE,@dt) AS NVARCHAR(200))),
        ( N'ss',dbo.PaddingLeft(CAST(DATEPART(SECOND,@dt) AS NVARCHAR(200)),'0',2)),
        ( N's',CAST(DATEPART(SECOND,@dt) AS NVARCHAR(200))),
        ( N'fffff',dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',5)),
        ( N'ffff',dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',4)),
        ( N'fff',dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',3)),
        ( N'ff',dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',2)),
        ( N'f',CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)));
    DECLARE @pt NVARCHAR(200),@vl NVARCHAR(200);
    DECLARE cus CURSOR FOR SELECT k,v FROM #tmm;
    OPEN cus;
    FETCH NEXT FROM cus INTO @pt,@vl;
    WHILE @@FETCH_STATUS=0
    BEGIN
        WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
        BEGIN
            SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
        END
        FETCH NEXT FROM cus INTO @pt,@vl;    
    END
    CLOSE cus;
    DEALLOCATE cus;
    DROP TABLE #tmm;
    SELECT @output;
END
例子：
DECLARE @output1 NVARCHAR(200),@dt1 DATETIME=dbo.GetLocalDate();
EXEC dbo.sp_datetimeformat @dt=@dt1, -- datetime
    @format = N'yyyy年MM月dd日 hh时mm分ss秒', -- nvarchar(200)
    @output=@output1 OUTPUT
SELECT @output1;
=========================================函数================================================
GO
ALTER FUNCTION DateTimeFormat(@dt DATETIME,@format NVARCHAR(500))
RETURNS NVARCHAR(500)
BEGIN
    DECLARE @pt NVARCHAR(200),@vl NVARCHAR(200),@output NVARCHAR(500)=@format;
    SELECT @pt=N'yyyy',@vl=CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'yyyy',@vl=CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'yyy',@vl=SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),2,3);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'yy',@vl=SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),3,2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'y',@vl=SUBSTRING(CAST(DATEPART(YEAR,@dt) AS NVARCHAR(200)),4,1);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'MM',@vl=dbo.PaddingLeft(CAST(DATEPART(MONTH,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'M',@vl=CAST(DATEPART(MONTH,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'dd',@vl=dbo.PaddingLeft(CAST(DATEPART(DAY,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'd',@vl=CAST(DATEPART(DAY,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'hh',@vl=dbo.PaddingLeft(CAST((CASE WHEN DATEPART(HOUR,@dt)<>12 THEN DATEPART(HOUR,@dt)%12 ELSE 12 END) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'h',@vl=CAST(CASE WHEN DATEPART(HOUR,@dt)<>12 THEN DATEPART(HOUR,@dt)%12 ELSE 12 END AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'HH',@vl=dbo.PaddingLeft(CAST(DATEPART(HOUR,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'H',@vl=CAST(DATEPART(HOUR,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'mm',@vl=dbo.PaddingLeft(CAST(DATEPART(MINUTE,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'm',@vl=CAST(DATEPART(MINUTE,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'ss',@vl=dbo.PaddingLeft(CAST(DATEPART(SECOND,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N's',@vl=CAST(DATEPART(SECOND,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'fffff',@vl=dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',5);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'ffff',@vl=dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',4);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'fff',@vl=dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',3);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'ff',@vl=dbo.PaddingLeft(CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200)),'0',2);
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    SELECT @pt=N'f',@vl=CAST(DATEPART(MILLISECOND,@dt) AS NVARCHAR(200));
    WHILE PATINDEX(CONCAT('%',@pt,'%') COLLATE Chinese_PRC_CS_AS_WS,@output)>0
    BEGIN
        SELECT @output=REPLACE(@output COLLATE Chinese_PRC_CS_AS_WS,@pt,@vl);
    END
    RETURN @output;
END

例子：
SELECT dbo.DateTimeFormat(dbo.GetLocalDate(),N'yyyy年MM月dd日 hh时mm分ss秒')