Creating a dynamic date range in SQL

Question

How can I construct a SQL statement that will always return a start date of July 1 of the previous year, and an end date of June 30 of the current year based on GETDATE()? Right now I have

    Dateadd(yy, Datediff(yy,1,GETDATE())-1,0) AS StartDate,
DateAdd(dd,-1,Dateadd(yy, Datediff(yy,0,GETDATE()),0)) AS EndDate

which will return January 1, 2012 and December 31, 2013 respectively..

Answer 1

You could just add another DATEADD() to your current script:

SELECT DATEADD(month,6,DATEADD(yy, DATEDIFF(yy,1,GETDATE())-1,0)) AS StartDate
      ,DATEADD(month,6,DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,GETDATE()),0))) AS EndDate

Answer 2

This seems like an odd request. One way of doing it is by constructing date strings and parsing them:

select cast(cast(year(GETDATE()) - 1 as varchar(255))+'-07-01' as DATE) as StartDate,
       cast(cast(year(GETDATE()) as varchar(255))+'-06-30' as DATE) as EndDate

This constructs the strings in the format '2013-06-30', which will be interpreted correctly on for most SQL Server date settings.

I believe (recalling something Aaron Bertrand wrote) that leaving out the hyphens always works:

select cast(cast(year(GETDATE()) - 1 as varchar(255))+'0701' as DATE) as StartDate,
       cast(cast(year(GETDATE()) as varchar(255))+'0630' as DATE) as EndDate

I, as a human, just much prefer having the hyphens.