Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)

I am having the below SQL query:

DECLARE @MyVar datetime = '1/1/2010'    
SELECT @MyVar

This normally returns '1/1/2010'.

What I need to do is have a list of dates, say:

1/1/2010
2/1/2010
3/1/2010
4/1/2010
5/1/2010

Then I need to FOR EACH through the numbers and execute the SQL Query.

Something like (pseudocode):

List = 1/1/2010,2/1/2010,3/1/2010,4/1/2010,5/1/2010

For each x in List
do
  DECLARE @MyVar datetime = x

  SELECT @MyVar

So this would return:-

1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010

I need this to return the data as one resultset, not the multiple resultsets, so I may want to use some sort of union at the end of the query, so every iteration of the loop unions onto the next.

3 Answers

0 votes
by (12.7k points)
edited by

SQL is primarily a set-orientated language - it is generally a bad idea to use a loop in it.

In this case, the same result could be achieved using a recursive CTE:

with cte as
(select 1 i union all
 select i+1 i from cte where i < 5)
select dateadd(d, i-1, '2010-01-01') from cte

Want to know more about SQL ? Join this SQL Certification course by Intellipaat.

If you want to learn SQL, refer to the below SQL tutorial video:

0 votes
by (37.3k points)

Using CTE:

A recursive CTE can be used to generate a sequence of dates

  • WITH NumberSequence AS (

  •     SELECT 1 AS i

  •     UNION ALL

  •     SELECT i + 1

  •     FROM NumberSequence

  •     WHERE i < 5

  • )

  • SELECT DATEADD(DAY, i - 1, '2010-01-01') AS MyDate

  • FROM NumberSequence

  • OPTION (MAXRECURSION 0);

Explanation

  1. Common Table Expression (CTE):

    • The CTE NumberSequence generates a sequence of integers from 1 to 5.

    • It starts with 1 and recursively adds 1 until it reaches 5.

  2. Date Calculation:

    • The SELECT statement uses DATEADD to calculate dates by adding i - 1 days to the base date 2010-01-01.

    • For each value of i, the corresponding date is calculated.

Alternatively, you can use a Temporary Table:

  • DECLARE @StartDate DATE = '2010-01-01';

  • DECLARE @Counter INT = 1;

  • CREATE TABLE #DateResults (MyDate DATE);

  • WHILE @Counter <= 5

  • BEGIN

  •     INSERT INTO #DateResults (MyDate)

  •     VALUES (DATEADD(DAY, @Counter - 1, @StartDate)); 

  •     SET @Counter = @Counter + 1;

  • END

  • SELECT * FROM #DateResults;

  • DROP TABLE #DateResults;

Explanation

  1. Temporary Table Creation: A temporary table #DateResults is created to store the generated dates.

  2. WHILE Loop: The loop generates dates as before but now inserts each date into the #DateResults table instead of selecting them directly.

  3. Final Selection: After the loop completes, a single SELECT statement retrieves all the dates from the temporary table.

  4. Cleanup: The temporary table is dropped at the end to free up resources.

0 votes
by (1.5k points)

This can be done in SQL without a loop by combining all of the dates into a single result set using a UNION ALL statement:

SELECT CAST('2010-01-01' AS datetime) AS MyVar

UNION ALL

SELECT CAST('2010-02-01' AS datetime)

UNION ALL

SELECT CAST('2010-03-01' AS datetime)

UNION ALL

SELECT CAST('2010-04-01' AS datetime)

UNION ALL

SELECT CAST('2010-05-01' AS datetime);

This query returns one result set including all the given dates.

Related questions

0 votes
2 answers
0 votes
3 answers
0 votes
2 answers

1.2k questions

2.7k answers

501 comments

693 users

Browse Categories

...