• Replies 3 replies
  • Subscribers 338 subscribers
  • Views 14 views
  • Users 0 members are here
Related

Date Table

Stephen Lang
$organization

I was wondering if there already existed a table in the impresario db with every date of the year, instead of me creating one?  I'm building a sales tracker in SSRS and would love to join it to every date so as to avoid missing dates in my graph.  I would love to know if this exists somewhere.

Thanks,

Stephen

Parents
  • $organization

    Stephen,

    I don't believe that there is much holding dates, besides TR_BATCH_PERIOD, but you could do something like the below with a common table expression:

     

    DECLARE @start_dt DATE = '2014-01-01';

    WITH dates (day_dt)
         AS (SELECT 0
             UNION ALL
             SELECT day_dt + 1
             FROM   dates
             WHERE  day_dt < 365)
    SELECT Dateadd(dd, day_dt, @start_dt)
    FROM   dates
    WHERE  Dateadd(dd, day_dt, @start_dt) < Dateadd(yy, 1, @start_dt)
    OPTION (maxrecursion 0); 

Reply
  • $organization

    Stephen,

    I don't believe that there is much holding dates, besides TR_BATCH_PERIOD, but you could do something like the below with a common table expression:

     

    DECLARE @start_dt DATE = '2014-01-01';

    WITH dates (day_dt)
         AS (SELECT 0
             UNION ALL
             SELECT day_dt + 1
             FROM   dates
             WHERE  day_dt < 365)
    SELECT Dateadd(dd, day_dt, @start_dt)
    FROM   dates
    WHERE  Dateadd(dd, day_dt, @start_dt) < Dateadd(yy, 1, @start_dt)
    OPTION (maxrecursion 0); 

Children
No Data