473,857 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamically return a range of dates?

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(CURDAT E(), 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
4 5408
Jorey Bump (de*****@joreyb ump.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(CURDAT E(), 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(CURDAT E(), INTERVAL I day) as end
from my_list
where i between 1 and 4;

--

This space not for rent.
Jul 23 '05 #2
yf***@vtn1.vict oria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
@news.victoria. tc.ca:
Jorey Bump (de*****@joreyb ump.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(CURDAT E(), 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(CURDAT E(), 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
Jorey Bump (de*****@joreyb ump.com) wrote:
: yf***@vtn1.vict oria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
: @news.victoria. tc.ca:

: > Jorey Bump (de*****@joreyb ump.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(CURDAT E(), 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(CURDAT E(), 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(CURDAT E(), 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
Jorey Bump wrote:
select DATE_ADD(CURDAT E(), 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
8689
by: manning_news | last post by:
Using SQL 2000. I need to select rows based on a date range. Both the beginning date and ending date of the range will be entered in the mm/dd/yy format. There are 2 columns in the table called MonthOfEval and YearOfEval, both varchar(9) and both with data like "August" and "2005-2006". I think I need to use these 2 columns to create a date in the mm/dd/yy format, then use that newly created date to see if it's within the date range. ...
5
2440
by: Dennis M. Marks | last post by:
After reading section 15.9.1.1 the ECMAScript Language Specifications I see that the date range for the Date function is +/- 100,000,000 days from 01 Jan 1970. This is called an extrapolated Gregorian calendar. Since the Gregorian calendar did not begin until 15 Oct 1582 what is the purpose of dates before that date? Wouldn't any computation prior to that date be meaningless or am I missing something? The reason I ask is that I have...
24
4452
by: PromisedOyster | last post by:
Is there a way that I can get a resultset that contains unique dates in a given date range without the need to have a temporary table and a cursor? perhaps something like: declare @start_date as datetime declare @end_date as datetime set @start_date as '1/1/2005' set @end_date as '1/1/2006'
3
7470
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The report prints dental and hygenist appointments for the date (one subreport for each). The user wants to enter a date range and have one page for each date in the date range. I'm wondering how to modify the report. The only way I see is to create...
3
3035
by: rugger81 | last post by:
I am currently working in the sql server 2000 environment and I want to write a function to pull all dates within a given date range. I have created several diferent ways to do this but I am unsatisfied with them. Here is what I have so far: declare @Sdate as datetime declare @Edate as datetime set @SDate = '07/01/2006' set @EDate = '12/31/2006'
1
1250
by: Tom O'Brien | last post by:
hello, This is a basic questions... i have just started using vb.... I want to figure out how to create a range dynamically. Say I have some data in a variable number of columns, in this example 3 - A, B, & C Eg.
3
2339
by: Deano | last post by:
The short version; In short, given one date range (start and end dates) how can I find the period that overlaps with another date range? The long version; I have knocked up a little application that helps my friend monitor employee absences. You can enter the start and end dates of an absence. For reports the user specifies start and end dates which produces a list of people with absences
1
2044
by: Kalkin | last post by:
Hi I am having trouble with creating a gridview dynamically. Any help would be much appreciated. The Problem: I have a range of dates that changes, i want those dates to be the column headers and thus cannot create the columns in the asp page. Also under each column must be a textbox, or at the very least an editable label. How do i go about creating this gridview, or if anyone has any ideas as to how to do this another way i...
0
9923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11082
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10394
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9549
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7104
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5967
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4190
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.