List Dates Between Min & Max Dates

I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Max
Dates
Example.
Min Date: 1/1/2005
Max Date: 5/1/2005
Dates between would be 2/1/2005,3/1/2005,4/1/2005
Please advise and thanks for your help.
GalahadThat's an interesting call. Here is my solution.
The idea is to get a list of sequential numbers: 1, 2, 3, ... To generate r
ows, I choose sys.all_columns view because it has probably the most number o
f rows (5000+ in a small database). This could be a weak point, but should s
atisfy normal business requirements.
select top(datediff(d,'2/1/2006','2/10/2006')+1)
cast('2/1/2006' as datetime)+
(select count(*) from sys.all_columns b where b.object_id*cast(1000 as bigin
t)+b.column_id<=a.object_id*cast(1000 as bigint)+a.column_id) - 1 val
from sys.all_columns a order by object_id, column_id
If you want only the first day of the month, it shouldn't be hard to accompl
ish with the same idea.
To get a comma delimited list, use the list() function I discussed about in
another thread:
select dbo.list(convert(char(10),val,101)) from ([The SQL Above]) tbl
"Galahad" <Galahad@.discussions.microsoft.com> wrote in message news:4339C758-529D-415C-BD23
-9C0F70EE1366@.microsoft.com...
> I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Ma
x
> Dates
>
> Example.
>
> Min Date: 1/1/2005
> Max Date: 5/1/2005
>
> Dates between would be 2/1/2005,3/1/2005,4/1/2005
>
> Please advise and thanks for your help.
>
> Galahad|||http://www.aspfaq.com/show.asp?id=2519
"Galahad" <Galahad@.discussions.microsoft.com> wrote in message
news:4339C758-529D-415C-BD23-9C0F70EE1366@.microsoft.com...
> I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min &
> Max
> Dates
> Example.
> Min Date: 1/1/2005
> Max Date: 5/1/2005
> Dates between would be 2/1/2005,3/1/2005,4/1/2005
> Please advise and thanks for your help.
> Galahad|||The callendar table is a brilliant solution:
http://www.aspfaq.com/show.asp?id=2519
Of course datatime is not the only data type that can be used.
Don't forget to thank the guys at aspfaq (http://www.aspfaq.com/credits.asp)
.
ML
http://milambda.blogspot.com/