SQL Server DateDiff Calculating Year, Quarter etc

I will admit, I simply do not follow the DateDiff, DateAdd functions that SQL provides for date calculation. It is too complex for me! However, I have had to use it for reporting year-to-date, quarter. While I am happy they provide these to make calculations easier, I just don't dig it.

I did find this 'SQL Tip of the Day' article very helpful.

Now, for posterity and my own future reference, if that link ever goes away I am listing them here too:

Usage #1 : Calculate Age
DECLARE @BirthDate DATETIME = '1932/06/12' SELECT DATEDIFF(YEAR, @BirthDate, GETDATE()) - CASE WHEN MONTH(@BirthDate) < MONTH(GETDATE()) OR (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) <= DAY(GETDATE())) THEN 0 ELSE 1 END AS [Age]

Usage #2 : Get Date Part of a DATETIME Value
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0) AS [Date Part Only]

Usage #3 : Get First Day of the Month, Quarter and Year SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) AS [First Day of the Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0) AS [First Day of the Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS [First Day of the Year]

Usage #4 : Get Last Day of the Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Year]

Usage #5 : Get First Day of the Following Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) AS [First Day of Next Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 1, 0) AS [First Day of Next Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) AS [First Day of Next Year]

Usage #6 : Get Last Day of the Following Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Year]

Usage #7 : Get First Day of the Previous Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Year]

Usage #8 : Get Last Day of the Previous Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Month]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Quarter]

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Year]