How to auto-add dates in column, SQL Server 2014

Question

I'm very new to SQL Server and I want to have dates from today up to 30 days ahead of todays date in one column, which way is the most considered efficient and "correct" way? ( I'm not asking for code ).

I read that loops should preferably be avoided in SQL Server, is that correct? Also, I thought of solving the date-issue with using a logon trigger (adding 30 days ahead of today whenever a logon happens), anyone know a more efficient and "correct" way?

Thanks

Can you give a little more context? Why do you want to add 30 days? Why on login? What does your application do? This is to make sure we are not dealing with an [XY problem](http://meta.stackexchange.com/a/66378/161449) — Andrew Savinykh, Mar 22 '15 at 20:20
Well I want to create a simple reporting solution, which would be directly linked to excel through add-in buttons, it would be a simple "to do list" or something of the like, and then I need to have a continuous column with dates — uncool, Mar 22 '15 at 20:24
But do you really need a consistent "30 days into the future" set of rows in your table? What exactly are the requirements here. Would it be okay if, when you inserted a new task/date into the table, you also inserted additional empty rows for each day that passed? Or do you really need this kind of set-up? Can't you just deal with "sparse" data, with only rows for where you have actual tasks/events? — pmbAustin, Mar 22 '15 at 20:59
Thing is I want to go back and fourth in the dates, just like a usual calender but now in "database format", it's somewhat strange, but this is only for learning purposes anyway. I'm just curious how one would do this the best way. — uncool, Mar 22 '15 at 22:04

score 1 · Answer 1 · answered Mar 23 '15 at 01:39

The most efficient way to do this would be a Job. SQL Server Agent provides the ability to run any script you want on any interval you choose. A very simplistic approach would be to create a job which runs nightly and inserts a row for [Today + 30 Days].

score 1 · Answer 2 · edited May 23 '17 at 12:12

I believe you are seeking 30 rows from a query with each row representing a date starting at today, and finishing 30 days after today.

There are many potential solutions for this that don't use a cursor/loop, for example

select
    dateadd(day,nums.number,nums.today) as a_date
from (
        select
          number
          , cast(getdate() as date) as today
        FROM master.dbo.spt_values as sv
        WHERE  sv.type = 'P'
                AND sv.number BETWEEN 0 and 29
       ) nums

see: this SQLfiddle demo

Note that query is using master.dbo.spt_values and some prefer not to use this (refer here). So instead you could use a small union all with cross join to generate the rows, or you can use a recursive "common table expression" (CTE) as an alternative.

;WITH
Digits AS (
          SELECT 0 AS digit UNION ALL 
          SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
          SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          )
, Tally AS (
          SELECT [tens].digit * 10 + [ones].digit AS number
          FROM Digits [ones]
          CROSS JOIN Digits [tens]
          )
select
    dateadd(day,nums.number,nums.today) as a_date
from (
        select
            number
          , cast(getdate() as date) as today
        FROM tally
        WHERE number BETWEEN 0 and 29
       ) nums

score 1 · Accepted Answer · answered Mar 23 '15 at 10:03

You can use recursive CTE to get sequential dates for next 30 days.

CREATE TABLE Dates
(
 allDates DATE
) 

;WITH MyCTE
 AS (SELECT getdate()                   AS ddate,
            dateadd(day, 30, getdate()) AS lastDate
     UNION ALL
     SELECT dateadd(day, 1, ddate),
            lastDate
     FROM   MyCTE
     WHERE  dateadd(day, 1, ddate) <= lastDate)

INSERT INTO Dates(allDates)
SELECT ddate FROM MyCTE

SELECT * FROM Dates

SQL Fiddle Demo

score 0 · Answer 4 · answered Mar 22 '15 at 22:03

0

To get todays date + 30 days do this:

select dateadd(dd,30,getdate())

answered Mar 22 '15 at 22:03

benjamin moskovits

5,261
1
12
22

How to auto-add dates in column, SQL Server 2014

4 Answers4