CODEWITHSUNDEEP
sqlsql-server-2008-r2
DotNetDudeinDC
DotNetDudeinDC

Reputation: 25

Creating datetime sequence in 2008 R2

I need to create a dateime sequence with minute variable time increments in a temp table . The output should look something like when 5 is used

2012-12-13 04:20:00.000
2012-12-13 04:25:00.000
2012-12-13 04:30:00.000
2012-12-13 04:35:00.000
2012-12-13 04:40:00.000
2012-12-13 04:50:00.000

Can this be done?

Upvotes: 0

Views: 571

Answers (2)

Dale M
Dale M

Reputation: 2473

I would suggest using a sequence table - every database should have one because they are so useful generating datetime sequences quickly and easily.

CREATE TABLE Sequence
(Number int PRIMARY KEY)

Now fill this table with the integers from 0 to 1,000,000 - don't worry, you only need to do this once.

You can then generate datetime sequences as long as you like (well up to 1,000,001) with a variation of

SELECT DATEADD(minute, Number * @stepsize, @StartDateTime)
FROM Sequence
WHERE Number<@NumberRequired

See this SQL Fiddle

This will generally be faster than using CTE and will be almost as fast as retrieving the info direct from a table. In fact, you may consider not using a temporary table but building a SP (or table valued function) with this at the guts as it will be roughly the same speed and a lot more flexible.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

WITH DateTimeSequence
AS
(
  SELECT CONVERT(datetime, '2012-12-13 04:20:00', 120) AS [datetime]    -- Start Date
  UNION ALL
  SELECT DATEADD(mi, 5, [datetime])
  FROM   DateTimeSequence
  WHERE  DATEADD(mi, 5, [datetime]) <= CONVERT(datetime, '2012-12-13 04:50:00', 120)      -- End Date
)
SELECT [datetime]
FROM   DateTimeSequence

Upvotes: 2

Related Questions