title: Calculate Fiscal Year
body: <p>I am generating a case statement that will be a master shared dataset for our report date ranges since we frequently use the same date ranges.  Currently I have previous week, previous month, previous year, current week, current month and current year.  I would like to add our fiscal year which is (10/1 to 9/30) as well but I haven't been able to come up with a pure SQL way to do it.  </p>
  
  <pre><code>DECLARE @frequency as nvarchar(20);
  SET @frequency = 'CURRENT MONTH'
  SELECT 
    CASE @frequency
      WHEN 'PREVIOUS MONTH' THEN CONVERT(DATE,DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)) 
      WHEN 'CURRENT MONTH' THEN CONVERT(DATE,DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()))))
      WHEN 'CURRENT WEEK' THEN CONVERT(DATE,DATEADD(dd, 1, DATEDIFF(dd, 0,DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATETIME)))))
      WHEN 'PREVIOUS WEEK' THEN CONVERT(DATE,DATEADD(dd, -6, DATEDIFF(dd, 0,DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATETIME)))))
      WHEN 'CURRENT YEAR' THEN CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
      WHEN 'PREVIOUS YEAR' THEN CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) 
    END as [START DATE],
  
    CASE @frequency
      WHEN 'PREVIOUS MONTH' THEN CONVERT(DATE,DATEADD(dd, 0, DATEDIFF(dd, 0,DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))))) 
      WHEN 'CURRENT MONTH' THEN CONVERT(DATE,DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101'))) 
      WHEN 'CURRENT WEEK' THEN CONVERT(DATE,DATEADD(dd, 1, DATEDIFF(dd, 0,DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATETIME)))))
      WHEN 'PREVIOUS WEEK' THEN CONVERT(DATE,DATEADD(dd, -6, DATEDIFF(dd, 0,DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATETIME))))) 
      WHEN 'CURRENT YEAR' THEN CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)))) 
      WHEN 'PREVIOUS YEAR' THEN CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))))
  END as [END DATE]
  </code></pre>