=======================================存储过程================================================
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秒')
