All dates between two dates

Hi everyone,
I have two fields StartDate and EndDate.
I would like to see all dates between these two dates.
Eg.
StartDate = 5/1/2005
EndDate = 5/5/2005
Result should be:
Date
5/1/2005
5/2/2005
5/3/2005
5/4/2005
5/5/2005
Any suggestions on how this can be accompilshed.
TIA,
Martin.From Itzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20030901', '20040831')
"martin" <kashaan007@.hotmail.com> wrote in message
news:eyr9yz6XFHA.2884@.tk2msftngp13.phx.gbl...
> Hi everyone,
> I have two fields StartDate and EndDate.
> I would like to see all dates between these two dates.
> Eg.
> StartDate = 5/1/2005
> EndDate = 5/5/2005
> Result should be:
> Date
> 5/1/2005
> 5/2/2005
> 5/3/2005
> 5/4/2005
> 5/5/2005
> Any suggestions on how this can be accompilshed.
> TIA,
> Martin.
>|||You can do it easely with a calendar table or an auxiliary number table.
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
AMB
"martin" wrote:
> Hi everyone,
> I have two fields StartDate and EndDate.
> I would like to see all dates between these two dates.
> Eg.
> StartDate = 5/1/2005
> EndDate = 5/5/2005
> Result should be:
> Date
> 5/1/2005
> 5/2/2005
> 5/3/2005
> 5/4/2005
> 5/5/2005
> Any suggestions on how this can be accompilshed.
> TIA,
> Martin.
>
>|||Here is much easier way:
declare @.startDate datetime
Declare @.EndDate datetime
set @.startDate = '5/1/2005'
set @.EndDate = '5/5/2005'
while (@.startDate < = @.EndDate)
Begin
print convert(char(12), @.startDate, 101)
set @.StartDate = dateadd(dd, 1, @.StartDate)
end
Thanks
Bindu
"martin" wrote:
> Hi everyone,
> I have two fields StartDate and EndDate.
> I would like to see all dates between these two dates.
> Eg.
> StartDate = 5/1/2005
> EndDate = 5/5/2005
> Result should be:
> Date
> 5/1/2005
> 5/2/2005
> 5/3/2005
> 5/4/2005
> 5/5/2005
> Any suggestions on how this can be accompilshed.
> TIA,
> Martin.
>
>