Looking for last Month

• DaveK2014

  Right there with Babe

  Points: 788

  More actions

  March 4, 2015 at 3:36 pm

  #315885

  I have a query that I need to run but I need it to look at Date A and if the month of Date A is last month it will be returned. The problem comes when I hit January of next year will it go back to December of the year before or think it should be December of the current year?

  So when the report is ran on January 15 2016 it needs to return all the results from December 2015.

  Thanks

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  March 4, 2015 at 4:50 pm

  #1781799

  There are a ton of date routines to do all sorts of things.

  Here is a very simplistic example:

  select Month(dateadd(mm, -1, '2016-01-15')) Mon,

  Year(dateadd(mm, -1, '2016-01-15')) Yr

  You can build on this and do all sorts of stuff. Check out datediff, datepart, datename, to name just a few.

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 4, 2015 at 7:47 pm

  #1781811

  DaveK2014 (3/4/2015)

  ---

  I have a query that I need to run but I need it to look at Date A and if the month of Date A is last month it will be returned. The problem comes when I hit January of next year will it go back to December of the year before or think it should be December of the current year?

  So when the report is ran on January 15 2016 it needs to return all the results from December 2015.

  Thanks

  The right way to do this (avoiding all the year/month date-part manipulation in the process) in all cases is to use what some people call a "Closed-Open Interval" or "Half Open Interval" where the start date is inclusive and the end date is exclusive. The generally accepted standard is to do something like this for your reporting queries.

  SELECT columnlist

  FROM dbo.YourTable

  WHERE SomeDateTimeColumn >= DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP),-1) --Beginning of previous month (inclusive)

  AND SomeDateTimeColumn < DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP), 0) --Beginning of current month (exclusive)

  ;

  The code above will always return the previous month's data based on whatever the current date is even when the year changes.

  The short explanation of the code is that the "0's" are shorthand for '1900-01-01' (base date) and "-1" is shorthand for '1899-12-31'. The DATEDIFFs count the number of monthly boundaries crossed since the base date and the DATEADDs convert that count of boundaries back to a date which will always be the first of the month for whatever month is returned. It won't work for dates before 1900 but it will work for all dates from 1900-01-01 thru 9999-12-31.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)