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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.