Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Generating Month Names Between Two Dates

Generating Month Names Between Two Dates

Generating Month Names Between Two Dates

Need help generating month names between two dates using CR 2016:

In Date = MAY 2017
Out Date = JAN 2018

I need:

MAY 2017
JUN 2017
JUL 2017
AUG 2017...
JAN 2018

The DateDiff only gives me a number of months between the two points. The DateAdd only gives me the end point which I already have. I need a formula or method to create the list of months between the two dates to use in a crosstab for summaries. Ultimate goal is to identify every month a record was "IN" but only using the start and end dates. I cannot use a calendar table and do not have access to the source database (Oracle).

Thanks for any help, in advance...

RE: Generating Month Names Between Two Dates

Thanks - this helps some but is dependent upon the dates being in the database. I don't have that situation - only the low and high end points of the range. A similar scenario to what I'm trying to solve would be extended stays at a hotel. 100 People person check-in MAY 2017 and they all check-out in different months until JAN 2018. I need to figure out how many people were checked-in at any point each month.

RE: Generating Month Names Between Two Dates

You can not show data that does not exist.
Ideally you need a table with all dates which can be your main table and then left join off this table to your data.
You can then display/group date data from this table. Months will then show even when no actual data in your main query exists.

Failing that you will need to convert report to a command and then create a temp table of dates you require to achieve the same results as above.


RE: Generating Month Names Between Two Dates


This is a nice piece of code (posted by somebody on Oracle forum, don't recall the name) that generates the calendar between two dates:
CONNECT BY LEVEL <= {?StopDate} - {?StartDate} + 1
) T
) N
) C

I hope it helps.


RE: Generating Month Names Between Two Dates


this helps some but is dependent upon the dates being in the database.
Your company, I’d wager, has a business calendar database table, and likely has a way for users like you to access. Ask your supervisor. Ask your IT liaison.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Generating Month Names Between Two Dates

What you are really trying to do is turn one record into several records. I have found that the cleanest way to do that is with an "inflation table":


If your time period can span up to x months then you will need an inflation table with x records which will create x duplicates of the original record. If these records are already numbered 1 to x, even better. If not, you can write a formula so that each of the records has a consecutive number. The inflation table number will be different in each of the duplicates. You can then use that number to add months to the start date so that each record becomes a different month's date. Then you can filter out the dates that are not between the begin and end dates of the original record.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close