Max Date

• shankar-131988

  SSC Enthusiast

  Points: 153

  More actions

  March 6, 2004 at 2:10 pm

  #101902

  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

  Shankar

• ray_higdon

  SSCarpal Tunnel

  Points: 4589

  More actions

  March 6, 2004 at 4:44 pm

  #497711

  select datepart(mm,dt) as Month,max(datepart(dd,dt)) as MaxDay

  from table

  group by datepart(mm,dt)

  ------------
  
  Ray Higdon MCSE, MCDBA, CCNA

• ray_higdon

  SSCarpal Tunnel

  Points: 4589

  More actions

  March 6, 2004 at 4:45 pm

  #497712

  Forgot the year, you could group by datepart(yy,dt) as well and include it in your select

  ------------
  
  Ray Higdon MCSE, MCDBA, CCNA