title: SQL date range Year-to-Date as of prior month end and previous year
body: <p>Have a query where the date ranges need to be year to date as of previous month's end and the respective date range of the previous year. I had a 'formula' that defined the date ranges by the numerical value of the month and year. e.g. previous month's end was current month minus one. This worked great until I hit January and being the first of the year and the I couldn't use the current year.</p>
  
  <p>I am not an experienced SQL user and have used google to learn most of what I know, but have not had success finding the answer to this problem -found a formula for prior month's end, but not the remaining three dates below.</p>
  
  <pre><code>DECLARE @PYStart datetime = '1/1/2015'
  DECLARE @PYEnd datetime = '12/31/2015' 
  DECLARE @CYStart datetime = '1/1/2016' -- first day of previous month's year
  DECLARE @CYEnd datetime = CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101) -- last day of past month
  
  SELECT YEAR(transaction_date) as 'Year', MONTH(transaction_date) as 'Month'
  
  FROM TABLE_Q1
  
  WHERE((transaction_date BETWEEN @pystart and @pyend) OR (transaction_date BETWEEN @cystart AND @cyend))
  
  group by year(transaction_date), month(transaction_date)
  </code></pre>
  
  <p>-Output below;
  <a href="https://i.stack.imgur.com/aWc49.png" rel="nofollow noreferrer">Example output if you were to run today</a></p>