Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 List all days of month

Author  Topic 

amitgup10
Starting Member

1 Post

Posted - 2006-06-20 : 08:44:51
I need a query that list all the days of a particular month like for April 2006 It will give the result
1/4/2006
2/4/2006.
.
.
.
.
.
.
.
30/4/2006

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-20 : 08:55:28
with nums (i)
as
(
select i = 0
union all
select i + 1 from nums where i < 100
)
select dte
from (select dte = dateadd(dd,nums.i,'20060401')
from nums) a
where dte < '20060501'
order by dte

or in v2000

select dte from
(select dte = dateadd(dd,i,'20060401') from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i + i7.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6 ,
(select i = 0 union select 64) as i7
) as ints
) dtes
where dte < '20060501'
order by dte


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 08:57:39
[code]This is one of the methods

Declare @date table(d datetime)
Declare @d datetime

set @d='20060401'

While @d<='20060430'
Begin
Insert into @date values (@d)
set @d=@d+1
End
Select d from @date
[/code]


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 10:29:13
Borrow MVJ's F_TABLE_DATE function from here
select DATE from dbo.F_TABLE_DATE ( '2006-04-01','2006-04-30')


KH

Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 10:33:43
And another one:

select DATE from dbo.F_TABLE_DATE('20060401','20060430')
...using this function...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 10:36:17




Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

sandeepmittal11
Starting Member

6 Posts

Posted - 2012-12-18 : 01:46:34
Refer this link
http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html

Regards,
Sandeep

My Blog : http://itdeveloperzone.blogspot.in
Go to Top of Page
   

- Advertisement -