Last 2 months Last working date

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  March 11, 2011 at 2:45 am

  #248033

  All,

  I have one scenario.

  create table #temp

  (

  dt datetime

  )

  insert into #temp

  select 'Dec 3 2010 12:00AM'

  union

  select 'Dec 10 2010 12:00AM'

  union

  select 'Dec 17 2010 12:00AM'

  union

  select 'Dec 24 2010 12:00AM'

  union

  select 'Dec 31 2010 12:00AM'

  union

  select 'Jan 7 2011 12:00AM'

  union

  select 'Jan 14 2011 12:00AM'

  union

  select 'Jan 21 2011 12:00AM'

  union

  select 'Jan 28 2011 12:00AM'

  union

  select 'Feb 4 2011 12:00AM'

  union

  select 'Feb 11 2011 12:00AM'

  union

  select 'Feb 18 2011 12:00AM'

  union

  select 'Feb 25 2011 12:00AM'

  union

  select 'Mar 4 2011 12:00AM'

  expected output:

  Jan 28 2011 12:00AM

  Feb 25 2011 12:00AM

  karthik

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  March 11, 2011 at 10:28 am

  #1297775

  SELECT Convert(varchar(25), Max(dt), 100)

  FROM #Temp

  WHERE DateDiff(Month, dt, Getdate()) BETWEEN 1 AND 2

  GROUP BY DateDiff(Month, dt, Getdate())

  ORDER BY Max(dt)

  I personally would return the dates in datetime format, rather than converting them to a specific character format.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA