T-SQL function to generate calendar table

问题:

I need to create user-defined function with two incoming parameters that returns a table. First parameter @start Date type. Second parameter @end Date type.

There is no any application to generate it. I just need a result in SQL server. I am looking for a simple T-SQL solution. SQL 2012 works for me.

Select * from Dbo.function_name(‘Oct 03, 2013’, ‘Nov 21, 2013’)

It has to return calendar table like this.

Su  Mo  Tu  We  Th  Fr  Sa
                3   4   5
6   7   8   9   10  11  12
13  14  15  16  17  18  19
20  21  22  23  24  25  26
27  28  29  30  31  1   2
3   4   5   6   7   8   9
10  11  12  13  14  15  16
17  18  19  20  21

回答1:

There is no simple solution. Having taken this on as a challenge to do while (whiskyglass.Contents.Any()), this is what I got.....

CREATE FUNCTION dbo.GenerateCalendar
(
@Startdate DATE, @EndDate DATE
)
RETURNS 
@Cal TABLE 
( Yr INT, Wk INT,Sun DATE, Mon DATE, Tue DATE, Wed DATE, Thu DATE, Fri DATE, Sat DATE)
AS
BEGIN
    DECLARE @DaystoBuild int
    DECLARE  @IntCal TABLE(MonthDate date, Dow INT, YearNum INT , WeekNum int)
    SET @DaystoBuild = DATEDIFF(day, @StartDate, @EndDate)

;WITH NumbersTable AS (
   SELECT 0 AS number
   UNION ALL
   SELECT number + 1
   FROM NumbersTable
   WHERE
   number <@DaystoBuild
 ),
MonthNums (BaseDate,[Index], MonthDate)
AS
( 
SELECT @StartDate, number, DATEADD(d, number, @StartDate)
FROM NumbersTable
)
INSERT INTO @IntCal
SELECT MonthDate, DATEPART(weekday, MonthDate) , DATEPART(year, MonthDate), DATEPART(week, MonthDate)
FROM MonthNums 

INSERT INTO @Cal
SELECT  *   FROM @IntCal
PIVOT
( MAX(MonthDate)
FOR Dow IN ( [1],[2],[3],[4],[5],[6],[7])
)AS F ORDER BY 1,2,3


    RETURN
END

It's not quite right - the output is a date rather than an integer and it doesn't behave nicely at the year boundary, but it might suffice for a purely T-SQL solution.

Added a copy to SQL Fiddle http://sqlfiddle.com/#!3/98ebe/2

回答2:

CREATE FUNCTION dbo.GenerateCalendar
(
@StartDate DATE, @EndDate DATE
)
RETURNS @Cal TABLE (Yr INT, Wk INT,Sun DATE, Mon DATE, Tue DATE, Wed DATE, Thu DATE, Fri DATE, Sat DATE)
AS
BEGIN
    DECLARE @DaystoBuild int
    DECLARE  @IntCal TABLE(MonthDate date, Dow INT, YearNum INT , WeekNum int)
    SET @DaystoBuild = DATEDIFF(day, @StartDate, @EndDate)
;
WITH NumbersTable AS (
   SELECT 0 AS number
   UNION ALL
   SELECT number + 1
   FROM NumbersTable
   WHERE
   number <@DaystoBuild
 ),
MonthNums (BaseDate,[Index], MonthDate)
AS
( 
SELECT @StartDate, number, DATEADD(d, number, @StartDate)
FROM NumbersTable
)
INSERT INTO @IntCal
SELECT MonthDate, DATEPART(weekday, MonthDate) , DATEPART(year, MonthDate), DATEPART(week, MonthDate)
FROM MonthNums 

INSERT INTO @Cal
SELECT  *   FROM @IntCal
PIVOT
( MAX(MonthDate)
FOR Dow IN ( [1],[2],[3],[4],[5],[6],[7])
)AS F ORDER BY 1,2,3
    RETURN
END
GO

select * from GenerateCalendar ('2013-10-12',DATEADD (MONTH,1,'2013-10-12'))