Get startedGet started for free

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.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

  • 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).

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

WITH time_series AS (
	SELECT 
  		-- Get the current time
	    ___ AS time
  	UNION ALL
	SELECT 
	    DATEADD(day, 1, time)
  	-- Call the CTE recursively
	FROM ___
  	-- Limit the time series to 1 year minus 1 (365 days -1)
  	WHERE time < GETDATE() + ___)
    
SELECT time
FROM time_series
-- Increase the number of iterations (365 days)
OPTION(MAXRECURSION ___)
Edit and Run Code