CODEWITHSUNDEEP
sqlasp-classicsql-server-2008-r2
Dwair
Dwair

Reputation: 67

Creating a list of all the dates between a range and then insert it

I have a problem were I need to insert a set of dates (one row for each date) that come from a date range that comes in to me as an ical format. I also need to insert the date if the date does not exist.

I'm happy manipulating the date formats so converting the incoming 20131201T000000 to smalldatetime is ok and I can build the SQL Not Exits as separate bits but how do I go about listing all the dates between 20131201 and 20140101 and then go about inserting them into the table if Not Exists along with some other data.

One idea I had would be to count the days using DateDiff which will give me a total number of days in the range and then I could DateAdd one day at a time and insert for each date until the DateDiff total is reached. However, it seems to be that its a very messy and long winded way to go about it and I would find it challenging to say the least.

Is there another method that someone could walk me through or point me in the right direction of?

Upvotes: 0

Views: 537

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

In my opinion you are much better off letting SQL Server determine the set of dates that are missing than to create a set of dates, then pull back all of the existing dates in the table to compare them to your set, then insert the ones that don't match. By doing this you are taking what SQL Server is really, really good at, tossing it in the mud, and re-inventing a wheel that is now quite non-circular.

So I suggest you do this in SQL Server.

Imagine you have an existing table with some dates:

CREATE TABLE dbo.MyTable(TheDate DATE);

And it has some existing days in the specified range:

INSERT dbo.MyTable(TheDate) VALUES
  ('20131203'),('20131205'),('20131209'),('20131230');

You can easily derive a set without using loops as follows.

CREATE PROCEDURE dbo.GenerateDates
  @start CHAR(17),
  @end   CHAR(17)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @s DATE = LEFT(@start, 8), @e DATE = LEFT(@end, 8);
  DECLARE @d SMALLINT = DATEDIFF(DAY, @s, @e);

  ;WITH x(d) AS 
  (
    SELECT TOP (@d) DATEADD(DAY, number, @s) 
     FROM master..spt_values
     WHERE type = N'P' ORDER BY number
  )
  -- INSERT dbo.MyTable(TheDate)
  SELECT d FROM x WHERE NOT EXISTS
    (SELECT 1 FROM dbo.MyTable WHERE TheDate = x.d);
END
GO

Sample usage:

EXEC dbo.GenerateDates
  @start = '20131201T000000', 
  @end   = '20140101T000000';

When you are happy this is giving the output you expect, alter the procedure and uncomment the INSERT.

(Also it would be best if you just pass date values from your app, instead of these 17-character strings, but I was working with what you have.)

Upvotes: 1

Panu Oksala
Panu Oksala

Reputation: 3438

Well lets see. You have a date called beginDate and date called endDate. Lets get all they days between those dates into list:

List<DateTime> betweenDates = new List<DateTime>();
var currentDate = beginDate;
while(currentDate <= endDate)
{
  betweenDates.Add(currentDate);
  currentDate = currentDate.AddDays(1);
}

Then lets fetch all the rows that are already in database:

// var datesInDb = SELECT DATE FROM TABLE_X WHERE DATE BETWEEN (beginDate, EndDate);

Now we have dates from database and all the dates that should be in database. Lets substract dates from database from our betweenDates

var datesWhichShouldBeInsertedIntoDB = betweenDates.Any(day => datesInDb.Contains(day) == false);

then insert datesWhichShouldBeInsertedIntoDB

Upvotes: 0

Related Questions