fill a years worth of datetimes

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  August 4, 2016 at 9:28 am

  #328635

  Hi I'm trying to fill datetimes for every minute of a year. 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.

  --Filling Dates

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

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

  select

  dateadd(d, number, @datestart)

  from master..spt_values

  where type='p'

  and number<=@days

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 4, 2016 at 9:49 am

  #1893855

  DECLARE @EndDate DATETIME = GETDATE()

  DECLARE @StartDate DATETIME = DATEADD(YEAR,-1,@EndDate)

  SELECT @StartDate, @EndDate;

  WITH t1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),

  t2 AS (SELECT n = 0 FROM t1 a, t1 b),

  t4 AS (SELECT n = 0 FROM t2 a, t2 b),

  t6 AS (SELECT n = 0 FROM t4 a, t2 b)

  SELECT TOP(1+DATEDIFF(MINUTE,@StartDate, @EndDate))

  Interval = DATEADD(MINUTE,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@StartDate)

  FROM t6

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Y.B.

  SSChampion

  Points: 11649

  More actions

  August 4, 2016 at 10:01 am

  #1893864

  Here's my stab at it...

  DECLARE @startdate DATETIME = '20160101'

  DECLARE @enddate DATETIME = '20161231'

  ;WITH Tally (n) AS

  (

  SELECT TOP (SELECT DATEDIFF(mi, @startdate, @enddate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

  FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)

  CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)

  CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)

  CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)

  CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)

  CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0)) f(n)

  )

  --SELECT * FROM Tally;

  SELECT DATEADD(mi, n-1, @startdate) FROM Tally

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned