-1

I created a Database called DatesOfYear

 CREATE TABLE DATESOFYEAR
 (
 MyName varchar(15),
 MyDate date
 );

and i inserted a value in it 

 INSERT INTO DATESOFYEAR VALUES ('FName','1-JAN-2017');

The output is 

 MYNAME     MYDATE
 FName      1-JAN-2017

How do i increment the MyDate from 1-JAN-2017 up until the end of year which is 31-DEC-2017, the output that i want to see is

 MYNAME     MYDATE
 FName      1-JAN-2017
 ....          ....
 FName      31-DEC-2017
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
Edward Garcia
  • 409
  • 6
  • 14
  • 1
    You've tagged SQL Server and MySQL. They are not one and the same. Which are you using? – Thom A Dec 08 '17 at 11:56
  • Thanks, i already updated it. – Edward Garcia Dec 08 '17 at 11:57
  • So are you asking for a way to not specify a value for `date` and have it get auto incremented to the next date? – Tim Biegeleisen Dec 08 '17 at 11:58
  • There's tonnes of questions already on Stack Overflow telling you how to generate a sequence of dates, did you try searching at all? – DavidG Dec 08 '17 at 11:59
  • Possible duplicate of [Generate Dates between date ranges](https://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges) – DavidG Dec 08 '17 at 12:00
  • You might as well use a dimdate table. Google "sql dimdate script" etc. One example is at https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho A general query from that would be `SELECT * FROM dimdate WHERE YEAR = '2017' ORDER BY datekey` – Zorkolot Dec 08 '17 at 14:01

2 Answers2

0

One option:

DECLARE @StartDate date, @EndDate Date;
SELECT @StartDate = '20170101', @EndDate = '20171231';

WITH Dates AS(
    SELECT @StartDate AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1,MyDate)
    FROM Dates
    WHERE DATEADD(DAY, 1,MyDate) <= @EndDate)
SELECT *
FROM Dates
OPTION (MAXRECURSION 366);

You can then, if you wish, INSERT that dataset into another table, or use it as is.

Thom A
  • 88,727
  • 11
  • 45
  • 75
0

You could make a loop and add rows in the database until you reach the end of the year. On each insert, you increment the previous date by 1 day.

DECLARE @my_previous_date DATE = '1-JAN-2017', 
        @my_date DATE = DATEADD(d,1, @my_previous_date);

WHILE @my_date <= '31-DEC-2017'
    BEGIN
    INSERT INTO DATESOFYEAR VALUES ('FName', DATEADD(d,1, @my_date));
    @my_date = DATEADD(d,1, @my_date);
END;
Sorix
  • 850
  • 5
  • 18