Suppose I want to store thousands of days in a table how will I retrieve it from the calendar?
This question is related to
sql
declare @date int
WITH CTE_DatesTable
AS
(
SELECT CAST('20000101' as date) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= '21001231'
)
SELECT [DWDateKey]=[date],[DayDate]=datepart(dd,[date]),[DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH , [date]),[MonthName]=DATENAME( MONTH , [date]),[MonthShortName]=substring(LTRIM( DATENAME(MONTH,[date])),0, 4),[Year]=DATEPART(YY,[date]),[QuarterNumber]=DATENAME(quarter, [date]),[QuarterName]=DATENAME(quarter, [date]) into DimDate FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
This SQL Server User Defined Function resolves the problem efficiently.No recursion, no complex loops. It takes a very short time to generate.
ALTER FUNCTION [GA].[udf_GenerateCalendar]
(
@StartDate DATE -- StartDate
, @EndDate DATE -- EndDate
)
RETURNS @Results TABLE
(
Date DATE
)
AS
/**********************************************************
Purpose: Generate a sequence of dates based on StartDate and EndDate
***********************************************************/
BEGIN
DECLARE @counter INTEGER = 1
DECLARE @days table(
day INTEGER NOT NULL
)
DECLARE @months table(
month INTEGER NOT NULL
)
DECLARE @years table(
year INTEGER NOT NULL
)
DECLARE @calendar table(
Date DATE NOT NULL
)
-- Populate generic days
SET @counter = 1
WHILE @counter <= 31
BEGIN
INSERT INTO @days
SELECT @counter dia
SELECT @counter = @counter + 1
END
-- Populate generic months
SET @counter = 1
WHILE @counter <= 12
BEGIN
INSERT INTO @months
SELECT @counter month
SELECT @counter = @counter + 1
END
-- Populate generic years
SET @counter = YEAR(@StartDate)
WHILE @counter <= YEAR(@EndDate)
BEGIN
INSERT INTO @years
SELECT @counter year
SELECT @counter = @counter + 1
END
INSERT @calendar (Date)
SELECT Date
FROM (
SELECT
CONVERT(Date, [Date], 102) AS Date
FROM (
SELECT
CAST(
y.year * 10000
+ m.month * 100
+ d.day
AS VARCHAR(8)) AS Date
FROM @days d, @months m, @years y
WHERE
ISDATE(CAST(
y.year * 10000
+ m.month * 100
+ d.day
AS VARCHAR(8))
) = 1
) A
) A
INSERT @Results (Date)
SELECT Date
FROM @calendar
WHERE Date BETWEEN @StartDate AND @EndDate
RETURN
/*
DECLARE @StartDate DATE = '2015-08-01'
DECLARE @EndDate DATE = '2015-08-31'
select * from [GA].[udf_GenerateCalendar](@StartDate, @EndDate)
*/
END
As this is only tagged sql (which does not indicate any specific DBMS), here is a solution for Postgres:
select d::date
from generate_series(date '1990-01-01', date '1990-01-01' + interval '100' year, interval '1' day) as t(d);
If you need that a lot, it's more efficient to store that in an table (which can e.g. be indexed):
create table calendar
as
select d::date as the_date
from generate_series(date '1990-01-01', date '1990-01-01' + interval '100' year, interval '1' day) as t(d);
This will create you the result in lightning fast.
select top 100000 identity (int ,1,1) as Sequence into Tally from sysobjects , sys.all_columns
select dateadd(dd,sequence,-1) Dates into CalenderTable from tally
delete from CalenderTable where dates < -- mention the mindate you need
delete from CalenderTable where dates > -- mention the max date you need
Step 1 : Create a sequence table
Step 2 : Use the sequence table to generate the desired dates
Step 3 : Delete unwanted dates
Source: Stackoverflow.com