Show Range of ALL Dates Between a Starting and Ending Date

[razorking]

I need to see if there is a way in a query to list rows of date vales between a given starting and ending date. Specifically the issue is - I have a table that has records with date values in a field. If for example my first date is 7/1/23 and my last date is 10/3/23 - I want to return a list of all dates equal to and after 7/1/23 and up to 10/3/23. Currently my table does not have a record representing each date in that span. I need all of the dates listed in the table and the ones missing from the span.

 

[theDBguy]

Hi. You could check out my blog on cartesian query.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

 

[CJ_London]

Would have thought a simple criteria would do it

where (myfield between [enter start date] and [enter end date]) or myfield is null

number of things to consider
1. Does myfield contain a time element? If so add 1 to the end date
2. Are you using UK or US date formats? - if UK you need to reformat your date prompts into the US style or the sql standard of yyyy-mm-dd

 

[razorking]

Hi. You could check out my blog on cartesian query.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

Thanks. This might work. I just have not figured out exactly how this translates to my query. I have one table and it has dates. like this:
1/03/2023
6/13/2023
6/30/2023
7/07/2023

I need to return a list of results showing the starting date (1/03/2023) and the ending date (7/07/2023) and a record for every date in-between.

 

[razorking]

Would have thought a simple criteria would do it

where (myfield between [enter start date] and [enter end date]) or myfield is null

number of things to consider
1. Does myfield contain a time element? If so add 1 to the end date
2. Are you using UK or US date formats? - if UK you need to reformat your date prompts into the US style or the sql standard of yyyy-mm-dd

Thanks but I don't think this will work for me. See my reply to theDBguy

 

[theDBguy]

Thanks. This might work. I just have not figured out exactly how this translates to my query. I have one table and it has dates. like this:
1/03/2023
6/13/2023
6/30/2023
7/07/2023

I need to return a list of results showing the starting date (1/03/2023) and the ending date (7/07/2023) and a record for every date in-between.

If you need help applying the technique to your database, consider posting a sample db with test data.

 

[CJ_London]

OK - on rereading the requirement, can see you need a second table to store all the dates in between. But I don't think you need a cartesian query (which has no joins) since you will need to left join the table with all the dates to the one with a few dates.

 

[ebs17]

Create a calendar table, i.e. a table with one field containing a consecutive date in a sufficient period of time.
Then you can create a query like this:

SELECT
   AnyDate
FROM
   CalendarTable
WHERE
   AnyDate BETWEEN
      (
         SELECT
            MIN(YourDate)
         FROM
            YourTable
      )
   AND
      (
         SELECT
            MAX(YourDate)
         FROM
            YourTable
      )

 

[razorking]

Create a calendar table, i.e. a table with one field containing a consecutive date in a sufficient period of time.
Then you can create a query like this:

SELECT
   AnyDate
FROM
   CalendarTable
WHERE
   AnyDate BETWEEN
      (
         SELECT
            MIN(YourDate)
         FROM
            YourTable
      )
   AND
      (
         SELECT
            MAX(YourDate)
         FROM
            YourTable
      )

I believe this will work. I was hoping to not have to create the table with all of the extra dates but I can live with that.

Thanks to everyone who replied!

One last thing - if anyone cares to respond. I still have one last issue that I think I can work around (somehow). My table with the actual dates (the one with the gaps between dates) is a link table and the date field is not actually a date field. It is a text field. Somehow I need ebs17 solution to convert the text dates to actual dates for the select/between query to work properly.

 

[Gasman]

I do not see why you cannot use theDBguy's logic?
You would use BETWEEN the min and max dates in your table, or whatever you supply.?

Investigate CDate()
Bad choice having dates as text, dates should be dates, end of.

 

[CJ_London]

I was hoping to not have to create the table with all of the extra dates but I can live with that.

you can do it with a small table storing the values 0 to 9 - I'll call the field num (a long) and the table tblNums

then you create a query which looks like this - call the query qryTally

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Tally]
FROM tblNums AS singles, tblNums AS tens, tblNums AS hundreds, tblNums AS thousands;

then to get your date range in another query you might have

SELECT CDate([Enter Start Date])+[Tally] AS CalenderDate
FROM qryTally
WHERE (((CDate([Enter Start Date])+[Tally])<=CDate([Enter End Date])))

 

[ebs17]

If the text expression is a valid date according to your regional settings, it can be converted into a date with CDate.

 

[ebs17]

I was hoping to not have to create the table

A well-planned calendar table can be used very often and in many different ways. The effort for one-off thinking and creating is put into perspective.