How get the last day of each month?

Hi All,
I need to do some calculation on the "date" data.
Kindly please advise me how to compose the SQL query on these:
1. How to get the last day of each month?
2. How to get the date of the first Sunday of each month?
3. How to get the date of the last Sunday of each month?
Thank you!
Michaeltry this
declare @.a datetime
set @.a = getdate(()
set datefirst 7
select dateadd(mm,datediff(mm,0,@.a)+ 1,0)-1 as last_date_of_month,
dateadd(mm,datediff(mm,0,@.a),0) + 8-
datepart(dw,dateadd(mm,datediff(mm,0,@.a)
,0)) as first_sunday,
dateadd(mm,datediff(mm,0,@.a)+ 1,0)-1 -
datepart(dw,dateadd(mm,datediff(mm,0,@.a)
+ 1,0)-2) as last_sunday
Let me know if this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Michael
Take a look at Steve Kass's greate example
create function dbo.NthWDay(
@.first datetime, --'20030801
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of w we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(wday,@.result))%7
end
"Michael" <vbado2003@.yahoo.com.sg> wrote in message
news:OYxMqsojGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I need to do some calculation on the "date" data.
> Kindly please advise me how to compose the SQL query on these:
> 1. How to get the last day of each month?
> 2. How to get the date of the first Sunday of each month?
> 3. How to get the date of the last Sunday of each month?
>
> Thank you!
> Michael
>