1

I hope I can explain this well enough. I'm trying to create a table with a date field that uses a date column from another table to grab the min and max dates from that column, and fills in all the dates in between.

So if the min and max dates were 1/1/2016 and 6/1/2016 then I would want the table to list every date in between those dates.

I am using SQL Server Management Studio 2014.

Thanks for your help.

Monday's Nightmare
  • 11
  • 1
  • 3
  • Quick Google search got me this 1. http://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server 2. http://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges 3. http://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates 4. http://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server – Pரதீப் Jun 25 '16 at 02:42

3 Answers3

0

One way to do it is using a recursive cte.

declare @mindate date = (select min(datecol) from tablename);
declare @maxdate date = (select max(datecol) from tablename);

with t(dt) as
(select @mindate 
 union all
 select dateadd(dd,1,dt) from t where dt < @maxdate)
select dt
into #temptable
from t

Example

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
0

Dates Table

Declare @Dates TABLE ([Date] DATE)
INSERT INTO @Dates VALUES ('1/1/2016'), ('6/1/2016')

Query to get Date range between Max and Min Dates

WITH X AS (
SELECT MinDate = MIN([Date])
      ,DateNum = DATEDIFF(DAY, MIN([Date]) , MAX([Date]))
FROM @Dates),
DateRange AS (
        Select 
            DATEADD(DAY, a.number, (Select MinDate FROM X)) Dates
        FROM
        (
            SELECT DISTINCT TOP (Select DateNum +1 FROM x ) a.number
            FROM master..spt_values a 
                   CROSS JOIN master..spt_values b
            WHERE a.number >=0
        )A
   )
Select * FROM DateRange
M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

If you wield the power, then I propose that you create a "date" table in your database. I find having a "date" table extremely useful for memorializing (and centralizing) certain attributes regarding dates as well as these types of needs (even if it involves a, wait for it, triangular join).

Creating the date table ...

It's pretty straight forward and feel free to load it up with columns, even if they are something that can be computed using native SQL Server functions. Having them physically stored sometimes provides more flexibility as well as a performance boost.

create table Dt (
    Dt date primary key clustered,
    BestDayEverBit bit not null default(0));

insert Dt (Dt)
select top (100000)
    dateadd(day, (row_number() over (order by (select null))), convert(date, '1900'))
from sys.all_columns a
cross join sys.all_columns b;

The query ...

declare
    @BegDt date = '2016-02-03',
    @EndDt date = '2016-02-13';

select *
from Dt
where Dt >= @BegDt
    and Dt < @EndDt;

Results

Dt         BestDayEverBit
---------- --------------
2016-02-03 0
2016-02-04 0
2016-02-05 0
2016-02-06 0
2016-02-07 0
2016-02-08 0
2016-02-09 0
2016-02-10 0
2016-02-11 0
2016-02-12 0

Hope this helps.

square_particle
  • 526
  • 2
  • 7