用函数格式化重新固化日期字符串
用函数格式化重新固化日期字符串
提问于 2013-08-15 00:08:29
嗨，我有个小功能，我不能被固定住。我通常会在应用层这样做，但没有选择，而且我的this有点生疏。
简化函数：(取一个日期并计算间隔的重复日期)
CREATE FUNCTION [dbo].[fn_GetRunDatesShort]
(@startDate DateTime, @weeks INTEGER)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @i INTEGER
DECLARE @interval INTEGER
DECLARE @outPut VARCHAR(500)
SET @outPut = ''
SET @i = 0
SET @interval = 7
WHILE (@i < @weeks)
BEGIN
SET @output = @outPut + ', ' +
Convert(varchar, DATEPART(MONTH,
DATEADD(d, @interval, @startDate)),101) + '-'
+ Convert(varchar, DATEPART(d,
DATEADD(d, @interval, @startDate)))
SET @interval = @interval + 7
SET @i = @i + 1
END
RETURN @output
END
GO
SELECT dbo.fn_GetRunDatesShort(GETDATE(), 4)所以这个回来了
, 8-21, 8-28, 9-4, 9-11我要把这个还给你
August 21, 28 | September 4, 11回答 2
Stack Overflow用户
回答已采纳
发布于 2013-08-15 01:28:32
尝试当您可以更有效地生成集合时，避免循环。和无长。
下面是一个将两种需求结合在一起的函数(生成日期集并以尴尬的输出格式格式化它们)：
CREATE FUNCTION dbo.fn_GetRunDatesShort_2
(
@StartDate DATE, @Weeks TINYINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX);
;WITH cte1(m,d) AS
(
SELECT DATENAME(MONTH, wd), CONVERT(VARCHAR(2), DAY(wd)) FROM
(
SELECT TOP (@Weeks) DATEADD(WEEK, ROW_NUMBER() OVER
(ORDER BY [object_id]), @StartDate)
FROM sys.all_objects ORDER BY [object_id]
) AS sq(wd)
),
cte2(ds) AS
(
SELECT DISTINCT m + STUFF((SELECT ', ' + d FROM cte1 AS cte1_a
WHERE cte1_a.m = cte1.m
FOR XML PATH(''), TYPE).value('.[1]','varchar(max)'),1,1,'')
FROM cte1
)
SELECT @output = STUFF((SELECT ' | ' + ds FROM cte2
FOR XML PATH(''),TYPE).value('.[1]','varchar(max)'),1,3,'');
RETURN (@output);
END
GO(如果您有一个Number表-- 你应该 --您可以将针对sys.all_objects的查询替换为针对Number表的类似查询，然后可以将WITH SCHEMABINDING添加到函数中--在本例中可能没有帮助，但它是只要有可能，我都会努力做到始终如一。)
用法：
SELECT dbo.fn_GetRunDatesShort_2(GETDATE(), 4);结果：
August 21, 28 | September 4, 11如果您的@weeks接近52，那么问题就出现了--如果您的日期是今年的8月和明年的8月，您期望的输出是什么？
编辑2014-05-19
为了确保订单朝着正确的方向(一些在去年8月进行的工作，但现在似乎在5月份更改了订单)，您可以对该函数进行如下更改：
CREATE FUNCTION dbo.fn_GetRunDatesShort_2
(
@StartDate DATE, @Weeks TINYINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX);
;WITH cte1(mn,m,d) AS
(
SELECT
DATEPART(YEAR, wd) * 100 + DATEPART(MONTH, wd),
DATENAME(MONTH, wd), CONVERT(VARCHAR(2), DATEPART(DAY,wd))
FROM
(
SELECT TOP (@Weeks) DATEADD(WEEK, ROW_NUMBER() OVER
(ORDER BY [object_id]), @StartDate)
FROM sys.all_objects ORDER BY [object_id]
) AS sq(wd)
),
cte2(mn,ds) AS
(
SELECT DISTINCT mn, m + STUFF((SELECT ', ' + d FROM cte1 AS cte1_a
WHERE cte1_a.m = cte1.m ORDER BY mn
FOR XML PATH(''), TYPE).value('.[1]','varchar(max)'),1,1,'')
FROM cte1
)
SELECT @output = STUFF((SELECT ' | ' + ds FROM cte2 ORDER BY mn
FOR XML PATH(''),TYPE).value('.[1]','varchar(max)'),1,3,'');
RETURN (@output);
END
GOStack Overflow用户
发布于 2014-05-19 19:02:56
为了避免使用FOR XML引擎，我可以建议一些case语句结构吗？
CREATE FUNCTION dbo.fn_GetRunDatesShort_2
(
@StartDate DATE, @Weeks TINYINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX)
SET @output = ''
SELECT @output = @output + CASE @output WHEN '' THEN M ELSE CASE R WHEN 1 THEN ' | ' + M ELSE '' END END + ' ' + CASE R WHEN 1 THEN '' ELSE ', ' END + D
FROM (
SELECT year(DT) as Y, datename(month,DT) as M, CAST(datepart(day,DT) AS VARCHAR) AS D
, row_number()over(partition by month(DT), year(dt) order by day(DT)) AS R
, row_number()over(order by dt) AS R2
FROM (
SELECT dateadd(ww,row_number()over(order by object_id), @StartDate) AS DT
FROM master.sys.objects
) z
) y
WHERE R2 <= @weeks
ORDER BY R2
RETURN @output
END
GOSELECT dbo.fn_GetRunDatesShort_2(GETDATE(),5)输出
May 26 | June 2 , 9 , 16 , 23如果日期的年份对输出很重要，也可以对其进行编辑。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18244333
复制相关文章
相似问题
腾讯云开发者
