# 'Exploding' datetime and duration

• ### Question

• Hi Everyone,

Is there a simple way that given a table with a datetime column and the number of days, I can 'explode' this to give a complete list of days. For example:

DateTime          Duration
01/01/09           2
01/02/09           3

Becomes:

01/01/09
02/01/09
01/02/09
02/02/09
03/02/09

(All dates in DDMMYY format)

Any help would be much appreciated.

Thanks!

Matt
Thursday, April 16, 2009 2:59 PM

• You can solve this having a table of numbers.

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO

INSERT Numbers(Number) SELECT n FROM fn_nums(1000000);
GO

select dateadd([day], Number - 1, [datetime]) as dt
from t inner join Numbers as n on n.Number <= t.duration
order by dt
GO

> (All dates in DDMMYY format)

What is the data type of [datetime]?

AMB
Thursday, April 16, 2009 3:12 PM