How can I generate a temporary table filled with dates in SQL Server 2000?

Question

I need to make a temporary table that holds of range of dates, as well as a couple of columns that hold placeholder values (0) for future use. The dates I need are the first day of each month between $startDate and $endDate where these variables can be several years apart.

My original sql statement looked like this:

select dbo.FirstOfMonth(InsertDate) as Month, 0 as Trials, 0 as Sales
into #dates
from customer
group by dbo.FirstOfMonth(InsertDate)

"FirstOfMonth" is a user-defined function I made that pretty much does what it says, returning the first day of the month for the provided date with the time at exactly midnight.

This produced almost exactly what I needed until I discovered there were occasionally gaps in my dates where I had a few months were there were no records insert dates. Since my result must still have the missing months I need a different approach.

I have added the following declarations to the stored procedure anticipating their need for the range of the dates I need ...

declare $startDate set $startDate = select min(InsertDate) from customer
declare $endDate set $endDate = select max(InsertDate) from customer

... but I have no idea what to do from here.

I know this question is similar to this question but, quite frankly, that answer is over my head (I don't often work with SQL and when I do it tends to be on older versions of SQL Server) and there are a few minor differences that are throwing me off.

Barnum

Use MASTER.dbo.SPT_VALUES to get a list of numbers, and use DATEADD to turn those into DATETIMEs – Irresponsive

Answer 1

This will quickly populate a table with 170 years worth of dates.

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2000',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

You can then use it by LEFT joining on to that table, for the range of dates you require.

Bernadette

I had to seed the CalendarMonths table with the basedate to get it to work: INSERT INTO @CalendarMonths SELECT @basedate I also changed the table to a table variable so that it was all temporary and in memory, too. – Phosphoroscope

@edmicman - Why temporary? Surely it's worth persisting/caching if you ever use it more than once? – Bernadette

in my specific case I'm wanting to get a list of months/years within an input date range. Maybe a static table would be good but I don't run into the need often enough to where just creating it on the fly is a problem. – Phosphoroscope

@PeterTirrell - a table variable is not stored in memory, it is stored as a temp table on disk. – Bowlder

just add 'insert into CalendarMonths select @basedate' (created a first line) just before the loop to be worked. – Torsion

Answer 2

I needed something similar, but all DAYS instead of all MONTHS.

Using the code from MatBailie as a starting point, here's the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31:

CREATE TABLE _Dates (
  d DATE,
  PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'

WHILE ( @dIncr < @dEnd )
BEGIN
  INSERT INTO _Dates (d) VALUES( @dIncr )
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END

Marguerita

Answer 3

This of course will not work in SQL-Server 2000 but in a modern database where you don't want to create a permanent table. You can use a table variable instead creating a table so you can left join the data try this. Change the DAY to HOUR etc to change the increment type.

declare @CalendarMonths table (date DATETIME,  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2014',
  @offset = 1
  INSERT INTO @CalendarMonths SELECT @basedate

WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
BEGIN
  INSERT INTO @CalendarMonths SELECT DATEADD(HOUR, @offset, date) FROM @CalendarMonths where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
  SELECT @offset = @offset + @offset
END

Oriana

Nothing leaps out at me as being not supported in SQL Server 2000 here. It had table variables – Burnedout

Answer 4

I would probably use a Calendar table. Create a permanent table in your database and fill it with all of the dates. Even if you covered a 100 year range, the table would still only have ~36,525 rows in it.

CREATE TABLE dbo.Calendar (
    calendar_date    DATETIME    NOT NULL,
    is_weekend       BIT         NOT NULL,
    is_holiday       BIT         NOT NULL,
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

Once the table is created, just populate it once in a loop, so that it's always out there and available to you.

Your query then could be something like this:

SELECT
    C.calendar_date,
    0 AS trials,
    0 AS sales
FROM
    dbo.Calendar C
WHERE
    C.calendar_date BETWEEN @start_date AND @end_date AND
    DAY(C.calendar_date) = 1

You can join in the Customers table however you need to, outer joining on FirstOfMonth(InsertDate) = C.calendar_date if that's what you want.

You can also include a column for day_of_month if you want which would avoid the overhead of calling the DAY() function, but that's fairly trivial, so it probably doesn't matter one way or another.

Signac

Answer 5

Tested below and it works, though it's a bit convoluted.

I assigned arbitrary values to the dates for the test.

DECLARE @SD smalldatetime,
        @ED smalldatetime,
        @FD smalldatetime,
        @LD smalldatetime,
        @Mct int,
        @currct int = 0

SET @SD = '1/15/2011'
SET @ED = '2/02/2012'


SET @FD = (DATEADD(dd, -1*(Datepart(dd, @SD)-1), @sd))
SET @LD = (DATEADD(dd, -1*(Datepart(dd, @ED)-1), @ED))

SET @Mct = DATEDIFF(mm, @FD, @LD)

CREATE TABLE #MyTempTable (FoM smalldatetime, Trials int, Sales money)

WHILE @currct <= @Mct
BEGIN
    INSERT INTO #MyTempTable (FoM, Trials, Sales)
    VALUES
    (DATEADD(MM, @currct, @FD), 0, 0)
    SET @currct = @currct + 1
END


SELECT * FROM #MyTempTable

DROP TABLE #MyTempTable

Agnew

Thank you @JNK, this is exactly what I needed. Also wanted to include DATEPARTs for a compare and demo to team. – Uncourtly

Answer 6

A starting point of a useful kludge to specify a range or specific list of dates:

SELECT *
FROM 
    (SELECT CONVERT(DateTime,'2017-1-1')+number AS [Date]
     FROM master..spt_values WHERE type='P' AND number<370) AS DatesList
WHERE DatesList.Date IN ('2017-1-1','2017-4-14','2017-4-17','2017-12-25','2017-12-26')

You can get 0 to 2047 out of master..spt_values WHERE type='P', so that's five and a half year's worth of dates if you need it!

Ponderous

Answer 7

Create a table variable containing a date for each month in a year:

declare @months table (reportMonth date, PRIMARY KEY (reportMonth));
declare @start date = '2018', @month int = 0; -- base 0 month
while (@month < 12)
begin
    insert into @months select dateAdd(month, @month, @start);
    select @month = @month + 1; 
end

--verify
select * from @months;

Demythologize

Answer 8

This is by far the quickest method I have found (much quicker than inserting rows 1 by 1 in a WHILE loop):

DECLARE @startDate DATE = '1900-01-01'
DECLARE @endDate DATE = '2050-01-01'

SELECT DATEADD(DAY, sequenceNumber, @startDate) AS TheDate
INTO #TheDates
FROM (
    SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n + 10000*tenthousands.n AS sequenceNumber
    FROM 
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n),
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tenthousands(n)
    WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n + 10000*tenthousands.n <= DATEDIFF(day, @startDate, @endDate)
    ) theNumbers

SELECT * 
FROM #TheDates
ORDER BY TheDate

NB: sql-server-2000 does not support Table Value Constructors but did support derived tables so

Replace all

(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))

With

(SELECT 0 UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9)

if working with that product

Urbanna

Answer 9

select top (datediff(D,@start,@end)) dateadd(D,id-1,@start)
from BIG_TABLE_WITH_NO_JUMPS_IN_ID

Libbie

Answer 10

declare @start datetime
set @start = '2016-09-01'
declare @end datetime
set @end = '2016-09-30'

create table #Date
(
    table_id int identity(1,1) NOT NULL,
    counterDate datetime NULL
);

insert into #Date select top (datediff(D,@start,@end)) NULL from SOME_TABLE
update #Date set counterDate = dateadd(D,table_id - 1, @start)

The code above should populate the table with all the dates between the start and end. You would then just join on this table to get all of the dates needed. If you only needed a certain day of each month, you could dateadd a month instead.

Hierology

Answer 11

For SQL Server 2000, this stackoverflow post looks promising for a way to temporarily generate dates calculated off of a start and end date. It's not exactly the same but quite similar. This post has a very in-depth answer on truncating dates, if needed.

In case anyone stumbles on this question and is working in PostgreSQL instead of SQL Server 2000, here is how you might do it there...

PostgreSQL has a nifty series generating function. For your example, you could use this series of all days instead of generating an entire calendar table, and then do groupings and matchups from there.

SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-04 12:00', '10 hours');
   generate_series   
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00
 2008-03-03 02:00:00
 2008-03-03 12:00:00
 2008-03-03 22:00:00
 2008-03-04 08:00:00
(9 rows)

I would also look into date_trunc from PostgreSQL using 'month' for the truncator field to maybe refactor your original query to easily match with a date_trunc version of the calendar series.

Gossamer

This doesn't answer the question, which is targeted towards SQL Server 2000. – Ultann

Updated to reference another stackoverflow post which does answer for SQL Server and clarified the portion of the answer that is for PostgreSQL – Gossamer

Answer 12

SELECT  P.Id
      , DATEADD ( DD, -P.Id, P.Date ) AS Date
    FROM  (SELECT TOP 1000 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS Id, CAST(GETDATE () AS DATE) AS Date FROM master.dbo.spt_values) AS P

This query returns a table calendar for the last 1000 days or so. It can be put in a temporary or other table.

Goatee

Not compatible with SQL Server 2000 – Burnedout

Answer 13

NB Not compatible with SQL Server 2000, but have left up in case it's useful for someone.

A recursive answer:

DECLARE @startDate AS date = '20220315';
DECLARE @endDate AS date = '20230316'; -- inclusive


WITH cte_minutes(dt) 
AS (
    SELECT 
        DATEFROMPARTS(YEAR(@startDate), MONTH(@startDate), 1)
    UNION ALL
    SELECT    
        DATEADD(month, 1, dt)
    FROM    
        cte_minutes
    WHERE DATEADD(month, 1, dt) < @endDate
)
SELECT 
    dt
into #dates
FROM 
    cte_minutes
WHERE
    dt >= @startDate
    AND
    dt <= @endDate
    
OPTION (MAXRECURSION 2000);



DROP TABLE dbo.#dates

Preposition

Not compatible with SQL Server 2000 – Burnedout

@MartinSmith Well that's a shame. – Preposition