June 11, 2008 at 1:23 pm
What I am looking for is a way to return all the days for 3 months using today's date. For example, if today is 6/11/08, the output would be...
2008-04-01 00:00:00.000
2008-04-02 00:00:00.000
2008-04-03 00:00:00.000
...
...
...
2008-06-30 00:00:00.000
any help would be much appreciated!
June 11, 2008 at 1:47 pm
Please read "Why should I consider using an auxiliary calendar table?" at
SQL = Scarcely Qualifies as a Language
June 11, 2008 at 1:51 pm
I would also vote for the use of a calendar table but just for the fun of it:
declare @mindate datetime, @maxdate datetime
select @mindate = left(convert(char(10),dateadd(m,-2,getdate()),112),6) + '01'
select @maxdate = dateadd(m, 3, @mindate) -1
select @mindate + number as dates
from master..spt_values
where type = 'P'
and number <= datediff(d,@mindate,@maxdate)
* Noel
June 11, 2008 at 2:02 pm
noeld (6/11/2008)
I would also vote for the use of a calendar table but just for the fun of it:
declare @mindate datetime, @maxdate datetime
select @mindate = left(convert(char(10),dateadd(m,-2,getdate()),112),6) + '01'
select @maxdate = dateadd(m, 3, @mindate) -1
select @mindate + number as dates
from master..spt_values
where type = 'P'
and number <= datediff(d,@mindate,@maxdate)
Thanks! That is one crazy script! I would also prefer a pre-populated table, but I dont have rights to create tables or do inserts on this particular db (reporting only) and would have to go through a change control for the dba to perform the task which may or may not be done by the end of this month 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply