SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 List all days of month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amitgup10
Starting Member

India
1 Posts

Posted - 06/20/2006 :  08:44:51  Show Profile  Send amitgup10 a Yahoo! Message  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
22755 Posts

Posted - 06/20/2006 :  08:57:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 06/20/2006 :  10:29:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 06/20/2006 :  10:33:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 06/20/2006 :  10:36:17  Show Profile  Reply with Quote




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

India
6 Posts

Posted - 12/18/2012 :  01:46:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000