Create a Calendar Table with Quarter Hour Granularity

Create a calendar table down to Week, Day, and 15 Minute Intervals

My week starts on a Sat and ends on a Fri

This code can create a calendar table with the Day, IsoWeek No., and Week No:
     SELECT  DATEADD(DAY, ROW_NUMBER() OVER ( ORDER BY A.object_id) - 1, '20000101') AS DayDate,
                YEAR(DATEADD(DAY, ROW_NUMBER() OVER ( ORDER BY A.object_id) - 1, '20000101')) AS YearNo,
                DATEPART(ISO_WEEK, DATEADD(DAY, ROW_NUMBER() OVER ( ORDER BY A.object_id) - 1, '20000101')) AS IsoWeekNo,
                DATEPART(WEEK, DATEADD(DAY, ROW_NUMBER() OVER ( ORDER BY A.object_id) - 1, '20000101')) AS WeekNo
        FROM    sys.objects A, sys.objects

Open in new window


Is there a way to take this calendar table down to the Quarter Hour?

So the end result may look like this:

DayDate
01/01/2000 00:00:00
01/01/2000 00:15:00
01/01/2000 00:30:00
01/01/2000 00:45:00
01/01/2000 01:00:00
01/01/2000 01:15:00
and so on until we get to the end of the day and until the completion of the calendar table.
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Add just 15 minutes Instead of days:

SELECT  DATEADD(MINUTE, 15 * ROW_NUMBER() OVER ( ORDER BY A.object_id), '20000101') AS DayDateTime,		
        YEAR(DATEADD(MINUTE, 15 * ROW_NUMBER() OVER ( ORDER BY A.object_id), '20000101')) AS YearNo,
        DATEPART(ISO_WEEK, DATEADD(MINUTE, 15 * ROW_NUMBER() OVER ( ORDER BY A.object_id), '20000101')) AS IsoWeekNo,
        DATEPART(WEEK, DATEADD(MINUTE, 15 * ROW_NUMBER() OVER ( ORDER BY A.object_id), '20000101')) AS WeekNo
FROM    sys.objects A, sys.objects;

Open in new window

0
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.