Thursday 22 August 2013

Split Date Range using SQL Server

Declare @StartDate DateTime, @EndDate DateTime, @FromDate DateTime, @ToDate DateTime, @CurrentCalYearStart DateTime, @CurrentCalYearEnd DateTime

Set @StartDate='06/25/1980'
Set @EndDate='06/26/1983'

SET @FromDate = @StartDate
SET @ToDate = @EndDate
WHILE (year(@FromDate) <= YEAR(@EndDate))
BEGIN
    SET @CurrentCalYearStart = DATEADD(MM,0,DATEADD(YY,DATEDIFF(YY,0,@FromDate),0))
    SET @CurrentCalYearEnd = DATEADD(MM,12,DATEADD(YY,DATEDIFF(YY,0,@FromDate),-1))
   
    IF (datediff(year,@StartDate,@CurrentCalYearStart) = 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) = 0)
    BEGIN
        SET @FromDate = @StartDate
        SET @ToDate = @EndDate
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) = 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) <> 0)
    BEGIN
        SET @FromDate = @StartDate
        SET @ToDate = @CurrentCalYearEnd
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) <> 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) <> 0)
    BEGIN
        SET @FromDate = @CurrentCalYearStart
        SET @ToDate = @CurrentCalYearEnd
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) <> 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) = 0)
    BEGIN
        SET @FromDate = @CurrentCalYearStart
        SET @ToDate = @EndDate
    END

SELECT @FromDate,@ToDate

SET @FromDate = DATEADD(YEAR,1,@FromDate)

END