The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.
What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.
SELECT TOP 86399
ROW_NUMBER() OVER (ORDER BY Id) AS Number, DATEADD(second, ROW_NUMBER() OVER (ORDER BY Id), CONVERT(datetime, DATEDIFF(DAY, 0, GETDATE()))) AS DateTime FROM [DB]..[LargeTable]
The number 86399 corresponds to the number of seconds (One day = (60*60*24) – 1) to generate in the table.