Need 1st and Last day of LAST QUARTER

• Express12

  SSCoach

  Points: 18514

  More actions

  March 22, 2006 at 11:36 am

  #113767

  Need to render 2 timestamps:  

  a) 1st day of LAST QTR w/ TIME=00:00:00.000

  SQL to get started with:

  >This SQL returns the 1st Day of "THIS" QTR; I need 1st Day of "LAST" QTR

  select

  DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

  b) LAST day of LAST QTR w/ TIME=23:59:59.997

  SQL to get started with:

  >This SQL returns the LAST DAY of Last QTR but TIME Is Not = 00:00:00.000 (I need TIME=23:59:59.997)

  SELECT dateadd(day,-day(min(getdate())),dateadd(month,-((month(min(getdate()))-1)%3),min(getdate())))

  >This SQL returns the LAST DAY of Last QTR w/ TIME=00:00:00.000

  SELECT

  CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,DATEADD(DAY,-DAY(MIN(GETDATE())),DATEADD(MONTH,-((MONTH(MIN(GETDATE()))-1)%3),MIN(GETDATE()))))))

  BT

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 22, 2006 at 12:08 pm

  #628188

  Hi Bill,

  Aren't you almost there once you've got the first day of this qtr (as you have). Just take off 3 months for a) and 3 seconds for b)...

  select dateadd(m, -3, DATEADD(qq, DATEDIFF(qq,0,getdate()), 0))

  select dateadd(s, -3, DATEADD(qq, DATEDIFF(qq,0,getdate()), 0))

  If I've misunderstood (likely) please provide examples of the dates/times you want

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 22, 2006 at 3:01 pm

  #628242

  This is how I do it

   

  select dateadd(qq,datediff(qq,0,GETDATE()),0)

  select dateadd(ms,-3,dateadd(qq,datediff(qq,0,GETDATE()) + 1,0))

• nigel.

  SSChampion

  Points: 11636

  More actions

  March 24, 2006 at 7:16 am

  #628604

  What about this:

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

  Nigel
  How to post questions