-
Jun 12th, 2016, 01:18 AM
#1
Thread Starter
Frenzied Member
how to query to list range dates
This involve to table field to query a result but I would like to list in rows a result of sql query between two dates. Im using mysql. I been thinking awhile now, i cant have idea of what statement to use.
I tried select curdate() as mycurdate where curdate() between '2001-01-01' and '2001-12-31' but I got error statement error.
I have hoping to have result
|mycurdate|
2001-01-01
2001-01-02
2001-01-03
.
.
.
2001-12-31
Last edited by codesearcher; Jun 12th, 2016 at 01:49 AM.
-
Jun 12th, 2016, 02:46 AM
#2
Re: how to query to list range dates
maybe
Code:
Private Sub Command1_Click()
Dim TheStartDate As Date
Dim TheEnddate As Date
Dim Adate As Date
TheStartDate = #1/1/2016#
TheEnddate = #12/31/2016#
For Adate = TheStartDate To TheEnddate
Debug.Print Adate
Next
End Sub
?
do not put off till tomorrow what you can put off forever
-
Jun 12th, 2016, 04:38 AM
#3
Re: how to query to list range dates
Why use curdate() ?
What is you table structure?
Show the fields...
-
Jun 12th, 2016, 07:23 AM
#4
Thread Starter
Frenzied Member
Re: how to query to list range dates
I have this sample transactions table, for example
tbl_transactions
field1: trdate
field2: userid
Now if I have records like
trdate | userid
01/01/2016 | 1
01/06/2016 | 2
01/11/2016 | 5
01/21/2016 | 10
I wanted to present a list from 01/01/2016 to 01/31/2016 like to
Date | No.of Users
01/01/2016 | 1
01/02/2016 | 0
01/03/2016 | 0
01/04/2016 | 0
01/05/2016 | 0
01/06/2016 | 1
01/07/2016 | 0
01/08/2016 | 0
01/09/2016 | 0
01/10/2016 | 0
01/11/2016 | 1
.
.
.
01/31/2016 | 0
As you can see, I have no table with data that has dates from 01/01 to 01/31/2016 so I cant do a relational query with it.
-
Jun 12th, 2016, 10:05 AM
#5
Re: how to query to list range dates
is that representative ?
01/01/2016 | 1
01/06/2016 | 2
01/11/2016 | 5
01/21/2016 | 10
or is the following also possible ?
01/01/2016 | 1
01/01/2016 | 2
01/01/2016 | 5
01/21/2016 | 10
and do you really want the dates that have no users included ?
edit:
As you can see, I have no table with data that has dates from 01/01 to 01/31/2016 so I cant do a relational query with it.
creating such a table is not rocket science, is it ?
Last edited by IkkeEnGij; Jun 12th, 2016 at 10:11 AM.
Reason: creating table
do not put off till tomorrow what you can put off forever
-
Jun 12th, 2016, 09:05 PM
#6
Thread Starter
Frenzied Member
Re: how to query to list range dates
Though is it possible to create a table with dates so I can do relational query to it, but I am thinking if it is also possible to get the same result without create a table with all the list of dates.
The tbl_transactions table can have the records you stated above IkkeEnGij.
-
Jun 13th, 2016, 01:41 AM
#7
Re: how to query to list range dates
Originally Posted by codesearcher
Though is it possible to create a table with dates so I can do relational query to it, but I am thinking if it is also possible to get the same result without create a table with all the list of dates.
Sure?
-
Jun 13th, 2016, 02:39 AM
#8
Re: how to query to list range dates
the following gives the number of users for every date that has at least 1 user
Code:
SELECT TheTable.TheDate, Count(TheTable.TheDate) AS NumberOfUsers
FROM TheTable
GROUP BY TheTable.TheDate;
if you join this with a table having all the dates, you have what you want
alternatively you could have a 2 column flexgrid with 1 column having all the dates
and fill the other column by looping trough the above
alternatively you could use access with a table having all the dates (maybe for a few centurys)
and a link to your table, and do the querying and joining in access
just a suggestion
do not put off till tomorrow what you can put off forever
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|