I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11
So in this case the table would start from 09/01/11 and store each day till it got to 10/10/11 I was wondering if there was a slick way of doing this in SQL Server - I am currently using SQL Server 2008. Thanks
Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:
DECLARE @StartDate DATE = '20110901'
, @EndDate DATE = '20111001'
SELECT DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
If you have a tally table, replace the subquery with the table. No recursion.
Try this if you are using SQL Server 2005 or newer:
WITH Dates AS (
SELECT
[Date] = CONVERT(DATETIME,'09/01/2011')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '10/10/2011'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 45)
A good example of cool stuff you can do with a CTE.
-- Declarations
DECLARE @dates TABLE(dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'
-- Query:
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
-- Output
SELECT * FROM @dates
Here is a solution that does not require recursion, and at the same time, this table-valued function is re-usable in many queries without the need to repeat the declaration of boilerplate variables again. This is the only alternative, for those who don't want recursion.
Create this simple function:
CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
@EndDate AS DATE,
@Interval AS INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
DECLARE @CUR_DATE DATE
SET @CUR_DATE = @StartDate
WHILE @CUR_DATE <= @EndDate BEGIN
INSERT INTO @Dates VALUES(@CUR_DATE)
SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
END
RETURN;
END;
And then select by:
select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)
I realize that this is an old thread, but I have to admit my dismay at the overabundance of recursive and looping solutions given here. I wonder just how many folks realize that recursion is nothing more than a very expensive loop? I understand the desire to create a Table-Valued Function, but I suggest that the following is far more efficient as it is set-based, without looping, recursion, or repeated single insert statements:
CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
SELECT DATEADD(DAY, n-1, @StartDate)
FROM cteTally
WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO
Use MVJ's F_TABLE_DATE function, it is purely awesome:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Once you implement this just pass in start and end date and you can insert all dates between.
Using @Abe Miesler's answer, for other's convenience I built it into a TVF for SQL Server 2008 onwards. It may help others - I had to find a way to include the CTE inside the TVF!
--Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
@EndDate AS DATE,
@interval AS INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
BEGIN
WITH Dates
AS (
SELECT [Date] = CONVERT( DATETIME, @startDate)
UNION ALL
SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
FROM Dates
WHERE Date < @EndDate)
INSERT INTO @Dates
SELECT [Date]
FROM Dates
OPTION(MAXRECURSION 900);
RETURN;
END;
This is an old thread, but in case it helps anyone, this is what I use in modern versions of SQL Server that support CTE's. This also gives you the Day of the Week and it can be tweaked to give other values you may need (i.e. Quarter, Month, etc.).
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '1/1/2020'
SET @EndDate = '12/31/2020'
DECLARE @DayTable Table(theDate date, theDayOfWeek nvarchar(50));
WITH DayTable AS (SELECT CAST(@StartDate AS DATETIME) theDate, DATENAME(dw, @StartDate) theDayOfWeek UNION ALL SELECT DATEADD(dd, 1, theDate), DATENAME(dw,DATEADD(dd, 1, theDate)) FROM DayTable s WHERE DATEADD(dd, 1, theDate) <= CAST(@EndDate AS DATETIME))
INSERT INTO @DayTable(theDate, theDayOfWeek) SELECT theDate, theDayOfWeek FROM DayTable OPTION (MAXRECURSION 365);
SELECT * FROM @DayTable
Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);
While @StartDate<=@EndDate
begin
SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
INSERT INTO @tempDateRange (DateFrom,DateThru)
VALUES
(
@StartDate,
@EndDate
)
SET @StartDate=DATEADD(DAY,1,@EndDate)
IF @EndDate< '2016-12-31'
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;
select * from @tempDateRange
+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru
If for some reason you can't declare variables, such as when using derived tables in Looker, you can go like this:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(
day,
convert(date, '2017-01-01'),
convert(date, '2018-12-31')
)
By the way, this is how your date series view could look like in LookerML:
view: date_series {
derived_table: {
sql:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
}
dimension: date {
primary_key: yes
type: date
sql: ${TABLE}.d ;;
}
}
CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)
Insert into #ProductSales
Values
(373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
(375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')
;WITH Dates AS (
SELECT ProjectiD
,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
FROM #ProductSales
UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
[Date] = DATEADD(MONTH, 1, [Date])
FROM
Dates
WHERE
Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
CAST([Date] as Date) Date
FROM
Dates
OPTION (MAXRECURSION 45)
