CODEWITHSUNDEEP
sqlsql-serverdatedate-range
FrozenYeti
FrozenYeti

Reputation: 33

Creating a dynamic date range in SQL

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..

Upvotes: 3

Views: 29133

Answers (4)

bretsbk
bretsbk

Reputation: 41

I've been using this CTE for dynamic fiscal year ranges based on the current date. It returns the start and end dates for the current fiscal year based on the current date.

WITH FYDates AS (
 SELECT 
    CASE 
        WHEN MONTH(GETDATE()) IN (1, 2, 3, 4, 5, 6) 
        THEN CAST(CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '/07/01' AS DATE)
        ELSE CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '/07/01' AS DATE) END AS FYStartDate, 
    CASE
        WHEN MONTH(GETDATE()) IN (1, 2, 3, 4, 5, 6)
        THEN CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '/06/30' AS DATE) 
        ELSE CAST(CAST(YEAR(GETDATE()) + 1 AS VARCHAR) + '/06/30' AS DATE) END AS FYEndDate
),

You can also create this as a view to reference that when needed.

Upvotes: 2

logixologist
logixologist

Reputation: 3834

This should work for you:

SELECT CAST('7/1/' + CAST(DATEPART(yy, Dateadd(yy, Datediff(yy,1,GETDATE())-1,0)) as varchar) as varchar) as startdate,
CAST('6/30/' + CAST(DATEPART(yy, Dateadd(yy, Datediff(yy,0,GETDATE()),0)) as varchar) as varchar) as enddate

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Related Questions