Inserting a date range into SQL Server using ASP.NET c#

Question

I am developing a web application which will allow users to enter a start date and an end date. As I would like this to show up later in a calendar, I believe I need to insert every date between the start date and end date into the database table. For example: 01/01/2012 02/01/2012 03/01/2012 04/01/2012

Is there an easy way of doing this when I have just the start date and end date, e.g. 01/01/2012 and 04/01/2012?

Thanks

Use a stored procedure which takes the start and end date, or since you're already running asp.net, just call INSERT from a loop. — lc.
– lc., Commented Aug 13, 2012 at 17:06
Please use unambiguous date formats (e.g. YYYYMMDD). It's not immediately obvious what country you're in and therefore whether 04/01/2012 is April 1st or January 4th. You can tell by reading a bit more, but why make that hard? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 13, 2012 at 17:18

Aaron Bertrand · Accepted Answer · 2012-08-13 17:13:48Z

1

Rather than a loop, you could try:

;WITH x(n) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1) 
   ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
   FROM sys.all_objects
)
-- INSERT dbo.table_name(date_column)
SELECT DATEADD(DAY, n, DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101'))
FROM x;

(Comment out the INSERT when you trust the logic and the output.)

Also if 2000+ days is not a large enough swing, you could change to sys.all_columns (7500+) or use your own numbers table.

answered Aug 13, 2012 at 17:13

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BLSully · Accepted Answer · 2012-08-13 17:15:26Z

0

I question the need to do this based on your stated requirements, however, this is how:

DECLARE @InsertDate DATETIME = @StartDate;
WHILE @InsertDate <= @EndDate
BEGIN
    INSERT dbo.MyTable (Date) VALUES (@InsertDate)
    SET @InsertDate = @InsertDate + 1 --SQL does implicit conversion from integer to date (1 = 1 day)
END

EDIT: to address comment, this is also valid (possibly recommended, though I've never had an issue with the implicit conversion when used with DATETIME)

    SET @InsertDate = dateadd(d, 1, @InsertDate)

edited Aug 13, 2012 at 17:15

answered Aug 13, 2012 at 17:08

BLSully

5,9291 gold badge29 silver badges46 bronze badges

2 Comments

Aaron Bertrand Over a year ago

I recommend against the implicit shorthand. This doesn't work with DATE, for example. Just type DATEADD, it's much safer and it doesn't change your billable hours.

Aaron Bertrand Over a year ago

You've never had an issue with the implicit conversion because you haven't tried it with DATE, or DATETIME2, or TIME, or DATETIMEOFFSET.

Collectives™ on Stack Overflow

Inserting a date range into SQL Server using ASP.NET c#

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related