4

I created several queries and needed some records with dates to join against.

For example:

CREATE TABLE #YearQuarter (Y int, 
    ,MonthStart smalldatetime NULL
    ,MonthEnd smalldatetime NULL
    , YqName nvarchar(7) COLLATE SQL_Latin1_General_CP1_CS_AS);

INSERT INTO #YearQuarter (Y, MonthStart, MonthEnd, YqName) VALUES 
  (2014, '2014-01-01 00:00:00', '2014-01-31 23:59:59'), 
  (2014, '2014-02-01 00:00:00', '2014-01-28 23:59:59'), -- leap year?!
   ...
  (2016, '2016-01-01 00:00:00', '2014-03-31 23:59:59'), 
   ...
  (2026, '2014-01-01 00:00:00', '2014-01-31 23:59:59') 
;    

Since this is cumbersome, error prone (leap years) and does not contain a lot of dates i wonder if there is a helper-table with dates (days, weeks, months, years) for the the last and next 10 years so it can be used to join against.

2
  • I don't think there is, but why not use a loop to add the dates yourself? Mar 30, 2016 at 6:46
  • I guess it could be done by hand. Although i do not know how to handle the leap years. But based on Using dates and time in sql server i assume that many people would find it helpfull to have a few helper tables. Mar 30, 2016 at 7:47

1 Answer 1

4

You can use the following query to generate a resultset for years and months:

select yr as year
    , datefromparts(yr, month, 1) as [start]
    , eomonth(datefromparts(yr, month, 1)) as [end]
from (
    -- a sequence of groups of 12
    select cast((seq-1)/12 as int) + 2014 as yr
         , ((seq-1)%12 + 1) as month
    from (
       -- a sequence of numbers [1,120]
       select top 120  -- 12 months * 10 years
              row_number() over (order by id) as seq
        from votes
    ) as base
) as yearmonths

This works by fetching the row_number() for a table first so you get a useable sequence of numbers that are one apart. Then you can project that sequence to any grouping you like, in this case groups of 12 by dividing and taking the modulo of the the sequence number. With those two you can use the datefromparts and eomonth functions of sql server to generate the dates you need.

I leave it to you if you want to insert that result in a temp table or CTE.

2
  • Thanks for the query - eomonth and datefromparts were new to me available since sql server 2012 which is fine for SEDE. What do you think about that SEDE would still benefit from having a helper table? Mar 30, 2016 at 20:39
  • @slackmuggle as this query is low impact to run when you need it I don't think there is a major use case for it, to be honest. Its implementation might also not be straightforward as it needs to be available in every database (it can be a view I guess) but atm the database refresh script is rather straight forward and there is no place I foresee where this table should live.
    – rene
    Mar 30, 2016 at 20:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .