Sins of Time , but its better to have time and date in two separate dimensions

Hats off to Steve

/** * Function: fn_GetTimestampRange
* Author: Steve Gray / steve.gray@cobaltsoftware.net
* Version: 1.0.0.0
* Date: 30 April 2010
*
* Parameters:
* @FromDate - Start of time-range
* @ToDate - End of time-range
* @IncludeSeconds - 0 = Minute level, 1 = Second level
**/
CREATE FUNCTION fn_GetTimestampRange(@FromDate DATETIME, @ToDate DATETIME, @IncludeSeconds BIT)
RETURNS TABLE
AS
RETURN (
/* Loop through the days in the range */
WITH DayCTE([Date])
AS
(
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(255), @FromDate, 20))
UNION ALL
SELECT DATEADD(DAY, 1, [Date]) FROM DayCTE WHERE Date < @ToDate
), /* Obtain a set of numbers 0..59 */
NumberCTE([Number])
AS
(
SELECT 0
UNION ALL
SELECT Number + 1 FROM NumberCTE WHERE Number < 59
)
SELECT
Result.DateKey,
DATEPART(YEAR, Result.DateKey) AS [Year],
DATEPART(QUARTER, Result.DateKey) AS [Quarter],
DATEPART(WEEK, Result.DateKey) AS [Week],
DATEPART(WEEKDAY, Result.DateKey) AS [WeekDayNumber],
DATENAME(WEEKDAY, Result.DateKey) AS [WeekDayName],
DATEPART(DAYOFYEAR, Result.DateKey) AS [DayOfYear],
DATEPART(MONTH, Result.DateKey) AS [Month],
DATEPART(DAY, Result.DateKey) AS [Day],
DATEPART(HOUR, Result.DateKey) AS [Hour],
DATEPART(MINUTE, Result.DateKey) AS [Minute],
DATEPART(SECOND, Result.DateKey) AS [Second]
FROM
(
SELECT
DATEADD(SECOND, COALESCE(SecondCTE.Number, 0),
DATEADD(MINUTE, MinuteCTE.Number,
DATEADD(HOUR, HourCTE.Number, DayCTE.Date))) AS DateKey
FROM
DayCTE
LEFT JOIN NumberCTE HourCTE ON HourCTE.Number
BETWEEN 0 AND 23
AND DayCTE.Date IS NOT NULL
LEFT JOIN NumberCTE MinuteCTE ON MinuteCTE.Number
BETWEEN 0 AND 59
AND HourCTE.Number IS NOT NULL
LEFT JOIN NumberCTE SecondCTE ON SecondCTE.Number
BETWEEN 0 AND 59
AND @IncludeSeconds = 1
AND MinuteCTE.Number IS NOT NULL
WHERE
DATEADD(SECOND, COALESCE(SecondCTE.Number, 0),
DATEADD(MINUTE, MinuteCTE.Number,
DATEADD(HOUR, HourCTE.Number, DayCTE.Date)))
BETWEEN @FromDate AND @ToDate
) RESULT
);