Get all the days during the months - SQL datetime problems

Sometimes we want to get all the days in the particular month and what's the tricks using SQL query?

This is the tricks used in SQL Server 2005 for returning results for the current month.

SELECT SUM(Amount) AS TotalAmount, CONVERT(CHAR(11),
Date, 106) AS DateFROM
dbo.SpendingGROUP BY DateHAVING (Date >=
CONVERT(DATETIME, dbo.Date(YEAR(GETDATE()), MONTH(GETDATE()), 1), 102)) AND
(Date < CONVERT(DATETIME, DATEADD(month, 1,

dbo.Date(YEAR(GETDATE()), MONTH(GETDATE()), 1)), 102))

what is 102? it is a formating number in which returns only date from datetime value.

dbo.Date? yes this is custom function which you have to add into your database to simplify the data manipulation.

"Create function Date(@Year int, @Month int, @Day int) begin return dateadd(day, @Day-1, dateadd(month, @Month-1, dateadd(year, (@Year-1900),0))) end"

Need more date functions?

go to http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx thanks to Jeff of the dbo.Date function