Need Last Day of Previous 2 quarters

• Express12

  SSCoach

  Points: 18514

  More actions

  March 21, 2006 at 2:45 pm

  #112336

  Based on current date, I need SQL to derive:

  -- the last day of the Last Quarter w/ TIME = 00:00:00.000

  -- the last day of 2 quarters ago w/ TIME = 00:00:00.000

  I can't seem to resolve this using this sql:

  declare

  @DateX datetime

  select

  @dateX = '2006-12-02'

  SELECT

  DATEADD(day,-1,(DATEADD(qq, -(DATEPART(qq,@DateX -1)), (DATEADD(qq,datediff(qq,0,@DateX ),0)))))

  --or---

  SELECT

  DATEADD(day,-1,(DATEADD(qq, -(DATEPART(qq,getdate()-1)), (DATEADD(qq,datediff(qq,0,getdate()),0)))))

   

   

  BT

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  March 21, 2006 at 3:20 pm

  #627984

  Modified from code I found in the scripts section of this site.

  (Sorry can't seem to find the original to give credit)

  declare @DateX datetime

  select @DateX = '02/12/2006'

  -- First day of quarter for date passed in

  select @DateX = dateadd(dd,-DAY(@DateX)+1,@DateX)

  select case month(@DateX)%3

                       when 1 then @DateX

                       when 2 then dateadd(month,-1,@DateX)

                       when 0 then dateadd(month,-2,@DateX)

                     end

  -- First day of the previous quarter

  select @DateX = dateadd(qq,-1,dateadd(dd,-DAY(@DateX)+1,@DateX))

  select case month(@DateX)%3

                       when 1 then @DateX

                       when 2 then dateadd(month,-1,@DateX)

                       when 0 then dateadd(month,-2,@DateX)

                     end

• nigel.

  SSChampion

  Points: 11636

  More actions

  March 24, 2006 at 7:23 am

  #628608

  Try this:

  select DATEADD(dd,-1,DATEADD(mm,3,DATEADD(qq, DATEDIFF(qq,0,DATEADD(mm,-3,getdate())), 0))),
         DATEADD(dd,-1,DATEADD(mm,3,DATEADD(qq, DATEDIFF(qq,0,DATEADD(mm,-6,getdate())), 0)))
  

  Nigel
  How to post questions