CODEWITHSUNDEEP
sql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Formatting Reccuring Date String with a Function

Hi I have a little function I can't get pinned down. I would generally do this in application layer but don't have the option with it and my T-SQL is a little rusty.

Simplified Function: (Take a date and calculate recurring dates on interval)

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)

So this returns 

, 8-21, 8-28, 9-4, 9-11

I need to get this to return 

August 21, 28 | September 4, 11

Upvotes: 0

Views: 91

Answers (2)

Jaaz Cole
Jaaz Cole

Reputation: 3180

To avoid going through the FOR XML engine, May I suggest some case statement structure?

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
GO

Output of SELECT dbo.fn_GetRunDatesShort_2(GETDATE(),5)

May 26 | June 2 , 9 , 16 , 23

This could also be edited in case year of the date becomes important to the output.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280477

Try to avoid loops when you can generate sets much more efficiently, and please stop declaring varchar without length.

Here is a function that combines both of your requirements (generate the set of dates and format them in your awkward output format):

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

(If you have a Numbers table - which you should - you can replace the query against sys.all_objects to a similar one against the Numbers table, and then you can add WITH SCHEMABINDING to the function - which may not help in this case, but it's something I try to do consistently whenever possible.)

Usage:

SELECT dbo.fn_GetRunDatesShort_2(GETDATE(), 4);

Results:

August 21, 28 | September 4, 11

The problem comes if your @weeks approaches 52 - what output do you expect if you have dates from August of this year and dates from August of next year?

EDIT 2014-05-19

To ensure ordering in the right direction (something that worked okay last August, but seems to change order now in May), you can make this change to the function:

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
GO

Upvotes: 1

Related Questions