insert into table n records with start date x and end date y

Edgard Yamashita
Edgard Yamashita used Ask the Experts™
on
i have table like this:

Id        Date         Anything


i need to make a query where the user will send as parameter start date / end date

so if the user sent for eg:

start date 2009-10-02
end  date 2009-10-05

i need to write in the table


Id           Date
1            2009-10-02
2            2009-10-03
3            2009-10-04
4            2009-10-05

but if it be sunday one of those dates then dont insert

i have no ideia how to this.. any help would be appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun KlineLead Software Engineer

Commented:
Something like this?
declare @startdate datetime, @enddate datetime
 
declare @dates table
(
    id int not null identity(1,1),
    date datetime
)
 
set @startdate = '10/1/2009'
set @enddate = '10/30/2009'
 
while @startdate <= @enddate
begin
    if(datepart(weekday, @startdate) != 1)
        insert into @dates (date) values (@startdate)
    set @startdate = dateadd(day, 1, @startdate)
end
 
select * from @dates

Open in new window

Here's a way to do it without looping at all.

Greg



;WITH Tally (N) AS 
(
	SELECT TOP (DATEDIFF(dd, @StartDate, @EndDate + 1)) ROW_NUMBER () OVER (ORDER BY sc1.Object_ID) AS N
	FROM Master.sys.All_Columns sc1 CROSS JOIN
		Master.sys.All_Columns sc2
)
SELECT N AS [ID],  DATEADD(dd, N - 1, @StartDate) AS [Date]
FROM Tally

Open in new window

Oops.  Forgot to take the Sundays out.  This assumes that Sunday is the first day of the week on your SQL Server.  You can check it by running SELECT @@DATEFIRST.  7 = Sunday.

Greg



;WITH Tally (N) AS 
(
	SELECT TOP (DATEDIFF(dd, @StartDate, @EndDate + 1)) ROW_NUMBER () OVER (ORDER BY sc1.Object_ID) AS N
	FROM Master.sys.All_Columns sc1 CROSS JOIN
		Master.sys.All_Columns sc2
)
SELECT ROW_NUMBER () OVER (ORDER BY N) AS [ID],  DATEADD(dd, N - 1, @StartDate) AS [Date]
FROM Tally
WHERE DATEPART(dw, DATEADD(dd, N - 1, @StartDate)) <> 1

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
SELECT * WHERE [Date] > = StartDate AND [Date] <= EndTate AND DATEPART (dw, [Date]) <> 1
Top Expert 2009

Commented:
Opps, forgot to sort the results...
SELECT * WHERE [Date] > = StartDate AND [Date] <= EndTate AND DATEPART (dw, [Date]) <> 1 ORDER BY [Id], [Date]
or
SELECT * WHERE [Date] > = StartDate AND [Date] <= EndTate AND DATEPART (dw, [Date]) <> 1 ORDER BY [Date], [Id]
Depends on how you want to sort...
Top Expert 2009

Commented:
Typos, sheesh! I'm sure you know when I typed EndTate I meant EndDate.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial