1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server

Exercise

Create a time series of a year

The goal of this exercise is to create a series of days for a year. For this task you have to use the following two time/date functions:

  • GETDATE()
  • DATEADD(datepart, number, date)

With GETDATE() you get the current time (e.g. 2019-03-14 20:09:14) and with DATEADD(month, 1, GETDATE()) you get current date plus one month (e.g. 2019-04-14 20:09:14).

To get a series of days for a year you need 365 recursion steps. Therefore, increase the number of iterations by OPTION (MAXRECURSION n) where n represents the number of iterations.

Instructions

100 XP
  • Initialize the current time as time.
  • Select the CTE recursively and combine the anchor and recursive member with the correct statement.
  • Limit the number of iterations to days in a year minus 1.
  • Increase the maximum number of iterations to the number of days in a year with OPTION (MAXRECURSION n).