| Author |
 Topic  |
|
|
amitgup10
Starting Member
India
1 Posts |
 Posted - 06/20/2006 : 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
United Kingdom
12543 Posts |
Posted - 06/20/2006 : 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. |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 06/20/2006 : 08:57:39
|
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
Madhivanan
Failing to plan is Planning to fail |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 06/20/2006 : 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
|
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 06/20/2006 : 10:36:17
|
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
sandeepmittal11
Starting Member
India
6 Posts |
|
| |
Topic |
|
List all days of month
