vijay vijay - 5 months ago 21
SQL Question

create date time table using sql preocedure

How I can create a table like this in Sql server using procedure?

Year Month Week Day Hour Min
2016 1 1 1 1 1
2016 1 1 1 1 2
- - - - - -
2016 1 1 1 1 60
- - - - - -
2016 1 1 1 2 60
- - - - - -
2016 1 1 1 60 60

Answer

With the help of CTE:

CREATE PROCEDURE dbo.dates
    @datestart date,
    @dateend date
AS
BEGIN
    SET NOCOUNT ON;

    WITH cte AS (
    SELECT CAST(@datestart as datetime) as d
    UNION ALL
    SELECT DATEADD(MINUTE,1,d)
    FROM cte
    WHERE d < DATEADD(minute,59,DATEADD(hour,23,CAST(@dateend as datetime)))
    )

    SELECT  DATEPART(YEAR,d) [Year],   
            DATEPART(MONTH,d) [Month],
            DATEPART(WEEK,d) [Week],
            DATEPART(DAY,d) [Day],
            DATEPART(HOUR,d) [Hour],
            DATEPART(MINUTE,d) [Min]
    FROM cte
    OPTION (MAXRECURSION 0)
END

EXEC dbo.dates '2016-01-01', '2016-12-31'

Output:

Year    Month   Week    Day Hour    Min
2016    1       1       1   0       0
2016    1       1       1   0       1
2016    1       1       1   0       2
2016    1       1       1   0       3
2016    1       1       1   0       4
2016    1       1       1   0       5
2016    1       1       1   0       6
2016    1       1       1   0       7
2016    1       1       1   0       8
....
2016    12      53      31  23      59
Comments