蛋疼的时候写了一个蛋疼的函数
-- =============================================-- Author: 小爱 -- Create date: 2012-03-26-- Description: 以不同的格式显示日期/时间数据 -- @date: 合法的日期-- @format: 规定日期/时间的输出格式-- =============================================IF OBJECT_ID('formatDate','FN') IS NOT NULL DROP FUNCTION [formatDate]GOCREATE FUNCTION [dbo].[formatDate](@date AS datetime, @format varchar(50))RETURNS varchar(50)ASBEGIN DECLARE @string varchar(50) -- ================================================ -- 填充日期/时间的输出格式 -- ================================================ ;WITH allowedTokens (id, code, value) AS ( SELECT id, code COLLATE Latin1_General_CS_AS, value FROM ( SELECT 1, 'YYYY', RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4) UNION ALL SELECT 2, 'YY', RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) UNION ALL SELECT 3, 'Y', CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) AS int) AS varchar(2)) UNION ALL SELECT 4, 'MM', RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2) UNION ALL SELECT 5, 'M', CAST(MONTH(@date) AS varchar(2)) UNION ALL SELECT 6, 'DD', RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2) UNION ALL SELECT 7, 'D', CAST(DAY(@date) AS varchar(2)) UNION ALL SELECT 8, 'HH', RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2) UNION ALL SELECT 9, 'H', CAST(DATEPART(hour,@date) AS varchar(2)) UNION ALL SELECT 10, 'hh', RIGHT('00' + CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2)),2) UNION ALL SELECT 11, 'h', CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2)) UNION ALL SELECT 12, 'mm', RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2) UNION ALL SELECT 13, 'm', CAST(DATEPART(minute,@date) AS varchar(2)) UNION ALL SELECT 14, 'ss', RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2) UNION ALL SELECT 15, 's', CAST(DATEPART(second,@date) AS varchar(2)) UNION ALL SELECT 16, 'fff', RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3) UNION ALL SELECT 17, 'f', CAST(DATEPART(millisecond,@date) AS varchar(3)) UNION ALL SELECT 18, 'tt', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END UNION ALL SELECT 19, 't', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END ) AS susbst (id, code, value) ), -- ================================================ -- 对列表进行格式化处理 -- ================================================ substitutions (id, code, value, maxval) AS ( SELECT ROW_NUMBER() OVER (ORDER BY id, set_id), code, value, COUNT(*) OVER () FROM ( SELECT 0 AS set_id, id, code, value FROM allowedTokens ) AS src ), -- ================================================ -- 使字符串区分大小写 -- ================================================ formatStrings (formatString) AS ( SELECT @format COLLATE Latin1_General_CS_AS ), -- ================================================ -- 使用CTE递归替换标记 -- ================================================ recursiveReplace AS ( SELECT s.id,REPLACE(f.formatString,s.code,s.value) AS formattedDate,s.maxval FROM formatStrings AS f INNER JOIN substitutions AS s ON s.id = 1 UNION ALL SELECT s.id, REPLACE(r.formattedDate, s.code,s.value) AS formattedDate,s.maxval FROM recursiveReplace AS r INNER JOIN substitutions AS s ON s.id = r.id + 1 ) -- ================================================ -- 结果的最后一行就是想要得到的结果 -- ================================================ SELECT @string=formattedDate FROM recursiveReplace WHERE id = maxval RETURN @string;ENDGOSELECT [dbo].[formatDate](GETDATE(), 'YYYY/MM/DD'), [dbo].[formatDate](GETDATE(), 'YYMMDD'), [dbo].[formatDate](GETDATE(), 'YYYY-MM-DD HH:mm:ss'), [dbo].[formatDate](GETDATE(), 'YYYY-MM-DD hh:mm:ss tt')