Auto increment Date field in MySQL

Databases
1

Hi ,

I have a table, does not contain a date field in it. It has day of week values 1 to 7. Considering that i retrieve these 7 rows from the table, I want an extra field, say ‘test_date’ created whose first row value will be a date I want to start with and then auto increment the date value for the next 6 rows. is there a way we can do this?

Thanks in Advance

2

assuming “dw” is the column from your table with the numbers 1 through 7…

SELECT '2010-05-09' + INTERVAL dw DAY AS running_date
  FROM daTable
3

Thanks, It is working absolutely fine. What if I would like to extend the requirement like this:

With the query you provided above, this is what we can get
1 - 2010-05-10
2 - 2010-05-11
3 - 2010-05-12
4 - 2010-05-13
5 - 2010-05-14
6 - 2010-05-15
7 - 2010-05-16

What if I want to get the below shown by still having 2010-05-09 as the date I have at hand, and along with that I have
a value from 1-7 from where I want the date to start incrementing(3 in this case)

3 - 2010-05-10
4 - 2010-05-11
5 - 2010-05-12
6 - 2010-05-13
7 - 2010-05-14
1 - 2010-05-15
2 - 2010-05-16

Thanks again:)

4

i’m surprised you even had to ask…

SELECT ‘2010-05-09’ + INTERVAL dw+1 DAY …

5

New to such queries, so could not understand much. But, the change you said just adds the date given. I had to start with the same date, but corresponding to a value between 1-7 and wrap over the date increment.

Understood the basic query suggested by you, made some changes to get what i wanted.

SELECT '2010-05-09' + INTERVAL dw-(3-1) DAY AS running_date,dw
  FROM test_table where dw >= '3'
union
 SELECT '2010-05-09' + INTERVAL dw+(8-3) DAY AS running_date,dw
  FROM test_table where dw < '3' ;

Here 3 can be anything between 1 and 7, which we have at hand.

Thanks Again. Problem Solved:)