Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Insert Given Date Range

Posted on 2010-01-04
12
Medium Priority
?
942 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Answering4uAnt
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 26170728
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26170756
Hello Answering4uAnt,

What is the structure of your tables now?

Regards,

Patrick
0
 
LVL 4

Author Comment

by:Answering4uAnt
ID: 26171197
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 26171371
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
 

Expert Comment

by:aholl
ID: 26171809
Can you me more clear, what do you need exacly?

Thank you!
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 26172178
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
 
LVL 4

Author Comment

by:Answering4uAnt
ID: 26172405
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
 
LVL 4

Author Comment

by:Answering4uAnt
ID: 26172694
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
 

Expert Comment

by:aholl
ID: 26172772
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
 
LVL 41

Expert Comment

by:Sharath
ID: 26172830
Can you provide your database version?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 26173100
Again, assuming SQL Server...


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

Author Comment

by:Answering4uAnt
ID: 26173141
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question