How to get 7 days before from today date in mysql using query?

Question

How to get 7 days before today date in mysql using query?

I want output like this example:-

  Date        Day 
13/2/2016  Saturday
14/2/2016  Sunday
15/2/2016  Monday
16/2/2016  Tuesday
17/2/2016  Wednesday
18/2/2016  Thursday
19/2/2016  Friday

Note: There is no such table in mysql for date showing. I think using like CURDATE() function in mysql something like that.

Possible duplicate of [SQL date intervals query](http://stackoverflow.com/questions/6137973/sql-date-intervals-query) — micstr, Feb 20 '16 at 08:31
can you provide example data? or you want such output without using any table, just select from dual? — mitkosoft, Feb 20 '16 at 08:46
Sorry sir it's my mistake. I have changed my question. Actually there is no table in mysql, but need output have to be like above. using like CURDATE() function in mysql something like that. How to get answer for my question? — , Feb 20 '16 at 09:20

Rogier Spieker · Accepted Answer · 2016-02-20T09:53:20.717

The question was edited to clarify there are no tables.

MySQL (to my knowledge) has no sequence generator, and in order to prevent a procedure here's a trick you can use to generate a list of dates.

SELECT DATE(NOW() - INTERVAL n DAY) AS date,
       DAYNAME(NOW() - INTERVAL n DAY) AS day
  FROM (
    SELECT 0 n
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
  ) q
  ORDER BY day ASC
;

Note that this is a very cumbersome approach, imagine what a range of more dates would look like. If you need this for a one-shot operation, it'll be fine, if you are going to use this on a regular basis, you may want to create a procedure for it.

The table based answer (perhaps useful to others)

MySQL supports the DATE_ADD and DATE_SUB functions, which allows you to subtract an INTERVAL. Internally this is optimised to the following syntax, which you may find more flexible

SELECT *
  FROM table
 WHERE field >= NOW() - INTERVAL 7 DAY;

Most 'time units' are supported (MINUTE, HOUR, WEEK, etc) and you can replace the NOW() part with a field name too.

The DATE_ADD manual has some examples on how to use INTERVAL.

Basic syntax:

date + INTERVAL expr unit
date - INTERVAL expo unit

And implementation examples:

mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
    -> '2009-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
    -> '2009-01-01'
mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
    -> '2004-12-31 23:59:59'

Excuse me sir and thank you for replay me! But there is no such table in mysql for date showing. I think using like CURDATE() function in mysql something like that. — , Feb 20 '16 at 09:23
You will have to replace `table` and `field` in the example query with your table and field name. Where the field should be one of the various date/time types MySQL offers. — Rogier Spieker, Feb 20 '16 at 09:37

How to get 7 days before from today date in mysql using query?

1 Answers1