BorlandTalk.com

Matthew Pascoe · Guest

would like to write an SQL statement that will return a row for each day
that is in a date range that I provide.

For example if the date range is 01-10-04 to 04-10-04 I would like the
following result set:

Day(DateTime data type)
01-10-04
02-10-04
03-10-04
04-10-04

I need to use this as the base for creating a more complex query.

Thanks for any help.

Matthew

Alain Quesnel · Guest

You could do that within a stored proc, or create a UDF that returns a table
type.

What RDBMS are you using?

--

Alain Quesnel
[email]alainsansspam (AT) logiquel (DOT) com[/email]

www.logiquel.com

"Matthew Pascoe" <mpascoe (AT) stocklogix (DOT) com.au> wrote

Wayne Niddery [TeamB] · Guest

Matthew Pascoe wrote:

Matthew Pascoe · Guest

SQL Server 2000
"Alain Quesnel" <alainsansspam (AT) logiquel (DOT) com> wrote

Dennis Passmore · Guest

Look up "between" in the Sql Books Online.

Dennis Passmore
Ultimate Software, Inc.

Alain Quesnel · Guest

Would something like this do the trick?

/*-------------*/
create function dbo.fnCreateDateList (@inStartDate datetime, @inEndDate
datetime)
returns @DateList table(ID int identity (1, 1), DateValue datetime)
as
begin
while @inEndDate >= @inStartDate
begin
insert into @DateList
values(@inStartDate)
set @inStartDate = @inStartDate + 1
end
RETURN
end
/*-----------*/

And to test it:

select * from dbo.fnCreateDateList ('2004-12-23', '2005-01-03')

You could use some error processing to detect a start date greater than or
equal to the end date.

--

Alain Quesnel
[email]alainsansspam (AT) logiquel (DOT) com[/email]

www.logiquel.com

"Matthew Pascoe" <mpascoe (AT) stocklogix (DOT) com.au> wrote

Matthew Pascoe · Guest

Hi Alain,

Thats a great solution, I'll have a play around with it.

Thanks for taking the time to help me out.

Kind Regards,
Matthew

"Alain Quesnel" <alainsansspam (AT) logiquel (DOT) com> wrote

Chuck Hall · Guest

I created a table with such dates by creating and joining three small tables, called TRACKER.MONTHS (MONTH field contains 1 to 12), TRACKER.DAYS (DAY field contains 1 to 31) and TRACKER.YEARS (field YEAR containing enough four digit years to meet my needs).

I create TRACKER.CALENDAR with the following query (Oracle syntax):

INSERT INTO TRACKER.CALENDAR SELECT TO_DATE(MONTH||'/'||DAY||'/'||YEAR,'MM/DD/YYYY') "FULLDATE"
FROM TRACKER.MONTHS,TRACKER.DAYS,TRACKER.YEARS
WHERE (MONTH IN (1,3,5,7,8,10,12))
OR ((MONTH IN (4,6,9,11)) AND (DAY<31))
OR ((MONTH=2) AND (DAY<29))
OR ((MONTH=2) AND (MOD(YEAR,4)=0) AND (DAY=29) AND ((MOD(YEAR,100)<>0) OR (MOD(YEAR,400)=0)))

This is called a "cartesian" join which joins all members of each table will all members of the other table(s). That is not often done intentionally.

Chuck Hall

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote: