Get First and Last Day of Any Year

Question

I'm currently trying to get the first and last day of any year. I have data from 1950 and I want to get the first day of the year in the dataset to the last day of the year in the dataset (note that the last day of the year might not be December 31rst and same with the first day of the year).

Initially I thought I could use a CTE and call DATEPART with the day of the year selection, but this wouldn't partition appropriately. I also tried a CTE self-join, but since the last day or first day of the year might be different, this also yields inaccurate results.

For instance, using the below actually generates some MINs in the MAX and vice versa, though in theory it should only grab the MAX date for the year and the MIN date for the year:

;WITH CT AS(
    SELECT Points
        , Date
        , DATEPART(DY,Date) DA
    FROM Table
    WHERE DATEPART(DY,Date) BETWEEN 363 AND 366
        OR DATEPART(DY,Date) BETWEEN 1 AND 3
)
SELECT MIN(c.Date) MinYear
    , MAX(c.Date) MaxYear
FROM CT c
GROUP BY YEAR(c.Date)

Answer 1

I should refrain from developing in the evenings because I solved it, and it's actually quite simple:

SELECT MIN(Date)
    , MAX(Date)
FROM Table
GROUP BY YEAR(Date)

I can put these values into a CTE and then JOIN on the dates and get what I need:

;WITH CT AS(
    SELECT MIN(Date) Mi
        , MAX(Date) Ma
    FROM Table
    GROUP BY YEAR(Date)
)
SELECT c.Mi
    , m.Points
    , c.Ma
    , f.Points
FROM CT c
    INNER JOIN Table m ON c.Mi = m.Date
    INNER JOIN Table f ON c.Ma = f.Date