Max Date

How do i find out the max date of a particular month in a table.

I have a records in the table like this

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this
OUTPUT
---
07 Jan 2004
06 Feb 2004
28 Mar 2004
05 Apr 2004

I want a SQL statement that can do this for me

Thanks
ShankarHehe, just answered you on SQLServerCentral :)

select Max(datepart(dd,datecolumn)) as Day,datepart(mm,datecolumn) as month, datepart(yy,datecolumn) as Year
from table
group by datepart(mm,datecolumn),datepart(yy,datecolumn)

HTH|||thanks for your help,

i got a kind off tricky requirement here.

the query that i write has to return me 6 records always.

The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.

my conditions are like this.

i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
then i need to go to current - 1 month and pick up that record.

I need to keep doing this until i get the 4 records that i want.

Let me give u the example for this.

Lets take the example that i have given previously.

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this

01 Jan 2004 --> First Record that is created
07 Jan 2004 --> Max of Jan
06 Feb 2004 --> Max of Feb
28 Mar 2004 --> Max of March
03 Apr 2004 --> I choose this record becoz the count for max months
is not equal to 4 and hence to make it 4 , i add this
05 Apr 2004 --> The most recent record

Hope this helps in understanding the requirement

Thanks
Shankar