user3476463 user3476463 - 1 year ago 56
SQL Question

fill a years worth of datetimes

Hi I'm trying to fill datetimes for every minute of a year, in sql server. I found the code below, which nicely fills all the dates for a year. I was wondering if there's an easy way to modify to get datetimes filled down to the minute.


declare @datestart date = '2010-1-1', @dateend date = '2016-10-31'

declare @days int = datediff(d,@datestart,@dateend)

dateadd(d, number, @datestart)
from master..spt_values
where type='p'
and number<=@days

Answer Source

It's easy to extend your original attempt to handle the minutes. Depending on how often you intend to do this you may need to optimize your approach. I imagine that a simple loop is sufficient for your needs.

create table #minutes (dt datetime not null);
declare @datestart datetime = '2016-01-01 00:00', @dateend datetime = '2016-01-03 23:59';

while @datestart <= @dateend
    insert into #minutes (dt)
    select dateadd(minute, number, @datestart)
    from master..spt_values 
            type = 'p' and number between 0 and 1439
        and dateadd(minute, number, @datestart) <= @dateend;
    set @datestart = dateadd(minute, 1440, @datestart);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download