Ian’s Online Technical Notebook

I regularly have the need to break down a date in SQL Server. I might need to produce a report by hour or 15 minute intervals so I find the following bit of code useful. It is heavily commented to give you an idea of how it works.

-- Variables to hold the start and end dates and the difference between them.
DECLARE @Start datetime;
DECLARE @End datetime;
DECLARE @Diff datetime;

-- Set the start and end date then calculates the difference.
SET @Start = '2000-01-01';
SET	@End = GETDATE();
SET @Diff = @End - @Start;

SELECT @Start [StartDate]
,    @End [EndDate]

-- The number of years between the two dates.
,    DATEDIFF(yy, 0, @Diff) [Years]

-- Add the difference in years to the start date to remove years from the result.
,    DATEDIFF(dd, DATEADD(yy, DATEDIFF(yy, 0, @Diff), @Start), @End) [Days]

-- Use of the DATEPART function to break down the remainder of the difference.
,    DATEPART(hour, @Diff) [Hours]
,    DATEPART(minute, @Diff) [Minutes]
,    DATEPART(second, @Diff) [Seconds]
,    DATEPART(ms, @Diff) [Milliseconds];

This then produces the following result.

I hope somebody finds that useful.