most recent august

• Stephen E. Cook

  SSCrazy

  Points: 2275

  More actions

  April 11, 2006 at 8:36 am

  #116875

  does anyone know of (or can anyone create off the top of their head) a formula for finding the date of the previous august, given a date?

  i can use a CASE statement i.e.

  CASE WHEN DATEPART(MONTH, @Date) >= 8 THEN CAST(DATEPART(YEAR, @Date) AS CHAR(4)) + '-08-01' ELSE CAST(DATEPART(YEAR, @Date) - 1 AS CHAR(4)) + '-08-01' END

  but i figured maybe someone would have something a bit nicer.

  -- Stephen Cook

• singhster

  Ten Centuries

  Points: 1387

  More actions

  April 11, 2006 at 8:46 am

  #631941

  This any better?

   

  select

  case when month(@date) > 8 then convert(varchar,year(@date))

  else convert(varchar,year(@date) - 1)

  end

    +  '-08-01' 

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  April 11, 2006 at 6:11 pm

  #631990

  declare @Date datetime

  select @Date = getdate()

  declare @m int set @m = 8

  --  @m is a number of a month you take care about. You need August - so it's 8.

  --  When you need another month just change value supplied to this parameter

  select dateadd(YY, case when Month(@date) < @m then -1 else 0 end + Year(@Date) - Year(0), dateadd(mm, @M-month(0), 0))

  _____________
  Code for TallyGenerator