SQL Insert Given Date Range

I have a SQL table which has various columns, one of them is the date (date only) and the others are peoples movements for that date.

There are better ways of doing this I know, but this is the most straight forward one for the requirement I had, all I do is insert new movements for given people on a daily basis - and datestamping them with the applicable date

What I want to add now, is the next 1 years worth of dates automatically so that my SQL table will have 365 new rows, start with 04/01/2010 and incrementing 1 day at a time until it reaches a given date. (03/01/2011 in this case).

I know it can be done with 2 columns, one having start_date and another having end_date and SELECT WHERE BETWEEN but I don't want to change the existing structure of the table if possible as most people have their movements for each day.

Thanks in Advance
LVL 4
Answering4uAntAsked:
Who is Participating?
 
BrandonGalderisiCommented:
What DB engine?  

If you are using SQL Server, this is a function that I use to generate dates.


To return 365 dates from a given date:

Example:
select * from [dbo].[fn_GetDateRange]('2010-01-04',0,365)
if object_id('[dbo].[fn_GetDateRange]') is not null
     drop function [dbo].[fn_GetDateRange]
go
create function [dbo].[fn_GetDateRange](@BaseDate datetime, @DaysBefore int, @DaysAfter int)
returns @Dates Table
   (theDate      datetime 
   )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       Given a base date, returns a range of 
*                   dates from @DaysBefore before the @BaseDate
*                   to @DaysAfter the @BaseDate.
*    
*
*************************************************************/
begin
declare    @minDate datetime
set @minDate=dateadd(d,-@DaysBefore,@BaseDate)

;with
       cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
insert into @Dates
select @MinDate
union
select dateadd(d,n,@MinDate) from nums 
where n<=@DaysBefore+@DaysAfter
order by 1


return
end
go

Open in new window

0
 
mankowitzCommented:
You can usually do this much more easily with a scripting language, but a stored procedure could help as well.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`add1year` $$
CREATE PROCEDURE `test`.`add1year` ()
BEGIN

DECLARE iter INTEGER DEFAULT 0;

iterwhile: WHILE iter < 365 DO
   SET iter = iter + 1;
   INSERT dog (`date`) values (adddate(now(), iter));
END WHILE iterwhile;

END $$

DELIMITER ;
0
 
Patrick MatthewsCommented:
Hello Answering4uAnt,

What is the structure of your tables now?

Regards,

Patrick
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Answering4uAntAuthor Commented:
Mank, I sort of see what you're doing but it doesn't make 100% sense and me trying to get it to work hasn't got far. (more SQL knowledge might have helped me)

Matthew, at the moment it is just a basic tabe with:

id (int)
date_field (date)

and then a few more nvarchar columns for the memebrs of staff working there, headed with their names.

All my system does it uses getDate() at the time the user runs the system, and gets movements for the select staff member for the current date - using the date_field column.

The reason I want to add 365 days in one go, is one of the staff members is now on maternity for 12 months so I want to keep the existing structure but rather than typing 365 days of the same thing I want to auto insert the dates (1 date per row for 12 months) and then UPDATE that persons column to say 'On Maternity'

Cheers
0
 
ahollCommented:
Can you me more clear, what do you need exacly?

Thank you!
0
 
mankowitzCommented:
nvchars? I guess you are using sql server. how about this:

CREATE PROCEDURE addyear()
BEGIN

DECLARE @iter INTEGER;
SET @iter = 0;

WHILE @iter < 365 DO
   SET @iter = @iter + 1;
   INSERT table (date_field) values (dateadd(day, @iter, CURRENT_TIMESTAMP);
END WHILE;

end
go
0
 
Answering4uAntAuthor Commented:
mank.
I'll try give that a go.

aholl.
I thought my original request was fairly clear  - maybe more clear to me as I know what i want.
I want to insert into the table 365 rows, the first one having a date_field of 04/01/2010, the second row having a date_field of 05/01/2010, the third having a date_field of 06/01/2010.....and so on up to 365 days / or however many days (if its easier to say insert dates from today until XX/XX/XXXX date then this will work just as well)

Thanks
0
 
Answering4uAntAuthor Commented:
Brandon, that is great for generating the dates, but i want them in an existing table.
I have tried to use INSERT INTO in the following way, with no luck

INSERT INTO MyTable(date_field)
VALUES
(select * from [dbo].[fn_GetDateRange]('2010/04/01',0,365))

Sorry if this is basic stuff, or I am approaching this from totally the wrong angle.

Cheers
0
 
ahollCommented:
so, you would like a function that would accept StartDate and HowManyDays who would insert into your table as many records for the specified parameters?

StoreProc is what you need then, you also got to keep in mind that if you run this proc every day then you will have n records inserted, unless you add logic to delete what ever records not in the range of date specified!

Am I in the correct path here or?

Thanks

I will provide the correct store procedure when I ear from you!!
0
 
SharathData EngineerCommented:
Can you provide your database version?
0
 
BrandonGalderisiCommented:
Again, assuming SQL Server...


INSERT INTO MyTable(date_field)
select theDate from [dbo].[fn_GetDateRange]('2010/04/01',0,365)
0
 
Answering4uAntAuthor Commented:
Brandon, yes it is SQL Server 2008, sorry for not making that clear at the start.
Your function and last answer has done the trick, I have my 1 years worth of dates auto inserted.

Thanks for your help, and thanks everyone else for their efforts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.