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)
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 begin insert into #minutes (dt) select dateadd(minute, number, @datestart) from master..spt_values where type = 'p' and number between 0 and 1439 and dateadd(minute, number, @datestart) <= @dateend; set @datestart = dateadd(minute, 1440, @datestart); end