get the first and last day two months ago

• raym

  SSCrazy

  Points: 2213

  More actions

  July 16, 2008 at 8:31 am

  #191017

  how do I get the first day and the last day of the month two months ago ?

  DECLARE @sql VARCHAR(8000)

  ,@FirstDay DATETIME

  ,@LastDay DATETIME

  ,@ThreeMonthsAgo CHAR(7)

  ,@SixMonths CHAR(7)

  ,@FiveMonths CHAR(7)

  -- First Day Of Current Month

  SET @FirstDay = (SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))

  -- Last Day of Current Month

  SET@LastDay = (SELECT DATEADD(MONTH, 1, GETDATE() - DAY(GETDATE()) + 1) -1)

  SET@ThreeMonthsAgo = (SELECT SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-3,GETDATE()),112),1,4) + '_' +

  SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-3,GETDATE()),112),5,2))

  SET@SixMonths = (SELECT SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-6,GETDATE()),112),1,4) + '_' +

  SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-6,GETDATE()),112),5,2))

  SET@FiveMonths = (SELECT SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-5,GETDATE()),112),1,4) + '_' +

  SUBSTRING(CONVERT(VARCHAR(6),DATEADD(MM,-5,GETDATE()),112),5,2))

  --SHOULD BE MONTH OF MAY "1ST DAY OF THE MONTH 2 MONTHS AGO"

  SELECT @FirstDay

  --SHOULD BE MONTH OF MAY "LAST DAY OF THE MONTH 2 MONTHS AGO"

  SELECT @LastDay

  --- SELECT YEAR & MONTH THREE MONTHS AGO .. I.E "2008_04"

  SELECT @ThreeMonthsAgo

  SELECT @SixMonths

  SELECT @FiveMonths

• Clay Colburn

  Valued Member

  Points: 66

  More actions

  July 16, 2008 at 2:22 pm

  #844065

  I think that this will solve what you are asking, let me know if I misunderstood what you are looking for.

  DECLARE

  @numMonthsINT

  ,@targetDateDATETIME

  ,@firstDayDATETIME

  ,@lastDayDATETIME

  SELECT @numMonths = 2

  SELECT @targetDate = DATEADD(MM, @numMonths * -1, GETDATE())

  SELECT @firstDay = CONVERT(VARCHAR(2), MONTH(@targetDate)) + '/01/' + CONVERT(CHAR(4), YEAR(@targetDate))

  SELECT @lastDay = DATEADD(dd, -1, DATEADD(MM, 1, @firstDay))

  SELECT

  @firstDay

  ,@lastDay

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 16, 2008 at 2:40 pm

  #844074

  Here is SQL that will do it, albeit without the variables:

  [font="Courier New"]SELECT

      DATEADD(DAY, -DAY(GETDATE()) + 1, DATEADD(MONTH, -2, GETDATE())) AS first_day_2_months_ago,

      DATEADD(DAY, -DAY(GETDATE()), DATEADD(MONTH, -1, GETDATE())) AS last_day_2_months_ago[/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  July 16, 2008 at 2:42 pm

  #844076

  Here are a few examples:

  SELECT dateadd(month, datediff(month, 0, getdate()), 0)

  ,dateadd(month, datediff(month, -1, getdate()), -1)

  ,dateadd(month, datediff(month, 0, getdate()) - 1, 0)

  ,dateadd(month, datediff(month, -1, getdate()) - 1, -1)

  ,dateadd(month, datediff(month, 0, getdate()) - 2, 0)

  ,dateadd(month, datediff(month, -1, getdate()) - 2, -1)

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 16, 2008 at 2:51 pm

  #844079

  Jeffrey Williams (7/16/2008)

  ---

  Here are a few examples:

  SELECT dateadd(month, datediff(month, 0, getdate()), 0)

  ,dateadd(month, datediff(month, -1, getdate()), -1)

  ,dateadd(month, datediff(month, 0, getdate()) - 1, 0)

  ,dateadd(month, datediff(month, -1, getdate()) - 1, -1)

  ,dateadd(month, datediff(month, 0, getdate()) - 2, 0)

  ,dateadd(month, datediff(month, -1, getdate()) - 2, -1)

  I like this solution as you can just substitute a variable for the "-2" in datediff(month, 0, getdate()) - 2 to get any number of months back.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question