By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 435,255 Members | 2,773 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

# Dynamically return a range of dates?

 P: n/a I can retrieve today's date: mysql> SELECT CURDATE() AS begin; +------------+ | begin | +------------+ | 2005-06-01 | +------------+ 1 row in set (0.00 sec) I can retrieve a date 3 days from now: mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end; +------------+ | end | +------------+ | 2005-06-04 | +------------+ 1 row in set (0.00 sec) How do retrieve the range? +------------+ | range | +------------+ | 2005-06-01 | +------------+ | 2005-06-02 | +------------+ | 2005-06-03 | +------------+ | 2005-06-04 | +------------+ 4 row in set (0.00 sec) Jul 23 '05 #1
Share this Question
4 Replies

 P: n/a Jorey Bump (de*****@joreybump.com) wrote: : I can retrieve today's date: : mysql> SELECT CURDATE() AS begin; : +------------+ : | begin | : +------------+ : | 2005-06-01 | : +------------+ : 1 row in set (0.00 sec) : I can retrieve a date 3 days from now: : mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end; : +------------+ : | end | : +------------+ : | 2005-06-04 | : +------------+ : 1 row in set (0.00 sec) : How do retrieve the range? : +------------+ : | range | : +------------+ : | 2005-06-01 | : +------------+ : | 2005-06-02 | : +------------+ : | 2005-06-03 | : +------------+ : | 2005-06-04 | : +------------+ : 4 row in set (0.00 sec) one technique create table my_list ( I int ); insert into my_list values (1); insert into my_list values (2); insert into my_list values (3); (etc) select DATE_ADD(CURDATE(), INTERVAL I day) as end from my_list where i between 1 and 4; -- This space not for rent. Jul 23 '05 #2

 P: n/a yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453 @news.victoria.tc.ca: Jorey Bump (de*****@joreybump.com) wrote:: I can retrieve today's date:: mysql> SELECT CURDATE() AS begin;: +------------+: | begin |: +------------+: | 2005-06-01 |: +------------+: 1 row in set (0.00 sec): I can retrieve a date 3 days from now:: mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end;: +------------+: | end |: +------------+: | 2005-06-04 |: +------------+: 1 row in set (0.00 sec): How do retrieve the range?: +------------+: | range |: +------------+: | 2005-06-01 |: +------------+: | 2005-06-02 |: +------------+: | 2005-06-03 |: +------------+: | 2005-06-04 |: +------------+: 4 row in set (0.00 sec) one technique create table my_list ( I int ); insert into my_list values (1); insert into my_list values (2); insert into my_list values (3); (etc) It's this part that I'm trying to avoid. I'm already using an intermediate table that stores a range of dates and related info (name of weekday, other formats, etc.). Since I can get the related info with a simple query using a date function, I could reduce the table to a single column of dates. But I'd like to go one step further and simply generate the dates dynamically with a single query, given a start and end date. Then I wouldn't need an intermediate table to get the dates for all Mondays in a given time period, for example. select DATE_ADD(CURDATE(), INTERVAL I day) as end from my_list where i between 1 and 4; As a minimalist, I'll admit that's pretty cool. :) My application is only concerned about dates relative to today, so this is a step closer to what I want. Thanks. Jul 23 '05 #3

 P: n/a Jorey Bump (de*****@joreybump.com) wrote: : yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453 : @news.victoria.tc.ca: : > Jorey Bump (de*****@joreybump.com) wrote: : >: I can retrieve today's date: : > : >: mysql> SELECT CURDATE() AS begin; : >: +------------+ : >: | begin | : >: +------------+ : >: | 2005-06-01 | : >: +------------+ : >: 1 row in set (0.00 sec) : > : >: I can retrieve a date 3 days from now: : > : >: mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end; : >: +------------+ : >: | end | : >: +------------+ : >: | 2005-06-04 | : >: +------------+ : >: 1 row in set (0.00 sec) : > : >: How do retrieve the range? : > : >: +------------+ : >: | range | : >: +------------+ : >: | 2005-06-01 | : >: +------------+ : >: | 2005-06-02 | : >: +------------+ : >: | 2005-06-03 | : >: +------------+ : >: | 2005-06-04 | : >: +------------+ : >: 4 row in set (0.00 sec) : > : > : > one technique : > : > : > create table my_list ( I int ); : > : > insert into my_list values (1); : > insert into my_list values (2); : > insert into my_list values (3); : > (etc) : It's this part that I'm trying to avoid. I'm already using an : intermediate table that stores a range of dates and related info (name of : weekday, other formats, etc.). Since I can get the related info with a : simple query using a date function, I could reduce the table to a single : column of dates. But I'd like to go one step further and simply generate : the dates dynamically with a single query, given a start and end date. : Then I wouldn't need an intermediate table to get the dates for all : Mondays in a given time period, for example. : > select DATE_ADD(CURDATE(), INTERVAL I day) as end : > from my_list : > where i between 1 and 4; : As a minimalist, I'll admit that's pretty cool. :) My application is only : concerned about dates relative to today, so this is a step closer to what : I want. Thanks. my_list would be created once with enough rows for your largest query and then left in place (i.e. it is not a temporary table). The where clause is used to select the number of rows (or limit). Alternatively... ...if you have a table with enough rows then use that instead. mysql doesn't have a rownum, but the following trick is based on a post by a guy named Jeff Cann (thanks Jeff!). E.g. I have a table named Files that has five rows, so I can use that to get a list of five numbers SET @rownum := 0; select DATE_ADD(CURDATE(), INTERVAL @rownum := @rownum+1 day) as day from Files; +------------+ | day | +------------+ | 2005-06-02 | | 2005-06-03 | | 2005-06-04 | | 2005-06-05 | | 2005-06-06 | +------------+ 5 rows in set (0.00 sec) This needs limit to control the number of rows (up to the number of rows available in the table). -- This space not for rent. Jul 23 '05 #4

 P: n/a Jorey Bump wrote: select DATE_ADD(CURDATE(), INTERVAL I day) as end from my_list where i between 1 and 4; As a minimalist, I'll admit that's pretty cool. :) My application is only concerned about dates relative to today, so this is a step closer to what I want. Thanks. How about this: SELECT CURDATE() + INTERVAL D.I DAY AS `END` FROM (SELECT 1 AS I UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D; (Requires MySQL 4.1 for the subquery.) Regards, Bill K. Jul 23 '05 #5

### This discussion thread is closed

Replies have been disabled for this discussion.