to query all days of a month

[vjw]

Hi, is there a way to query all days of a given month?
for ex: there should be 31 records for January..28 for february like that..

[dhutton]

Try this - When I want to Query all the days in a month - for example July1 thru July31 would be:

SELECT MyData

FROM MyTable

WHERE MyDate BETWEEN '20070701' AND '20070731'

The date format has to be the same as whats in your table - provided that you DO have a Date field.

[ck9663]

Try this - When I want to Query all the days in a month - for example July1 thru July31 would be:

SELECT MyData

FROM MyTable

WHERE MyDate BETWEEN '20070701' AND '20070731'

The date format has to be the same as whats in your table - provided that you DO have a Date field.

just in case you don't have a datefield, put this in a function. it's currently defaulted to the current year. you can pass the year to the function, the code is already handling leap year...

 
select top 12 
datename(month,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))),
datename(year,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))),
datepart(dd,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))) as numofday
from sysobjects
where id <= 12
order by id asc