Hi,

I want the users to select the start date and end date. The dates have to be dynamic. For example if he runs report today...he has to see the dates starting from 2012_04 to 2013_04.

Please let me know if anyone has the solution for this.

Thank you,
priya

This message has been edited. Last edited by: Kerry,

Priya,

This is the time that the search tool above would prove very useful as very similar questions have been asked and answered before.

T

Hi Tony...
For some reason, I could not find the one i was looking for....Please let me know if anyone has any suggestion for me....

You didn't give us whole lot to work from so our understanding of the problem is the same as Tony's. Since he knows as much as anyone what is available on the forum, I would guess that your question has been answered based on our understanding of what you need. If it's not what you need, we might need a better explanation of the problem.

That being said, here's my suggestion: You know what the system month and year are for today from &YYM. Using Dialogue Manager, do some addition from those values and calculate your date ranges. And I KNOW there are examples of that on the forums in case you need help with that.

Use the system & variables
eg:
&YYMD always gives you today's year month and day
You can parse it into whatever you need,
and use the AYMD or AYM functions to operate on them to back up a year or a few months or a few days
&DATE does as well, albeit formatted.
There are a zillion variations on those two variables.

-SET &thisyear = &YYMD / 10000 ;
-SET &lastyear = &thisyear - 1 ;
-TYPE &YYMD &DATE &thisyear &lastyear

I define two fields.
START_DATE = '&DATEYYM' END_DATE = START_DATE+12

My date field is CAPACITY_DATE

So I have to create prompts one for start_date and next for the end_date.

Please suggest on how to create prompts for both defined fields.

Thank you,
Priya.

Creating the prompt is going to depend on how you launch your program. Are you creating an HTML launch page? Are you executing the program from MRE?

quote:

to depend on

I am executing the procedure from mre

Then make sure that under properties the Prompt for Parameters is checked and you are using &START_YYMD AND &END_YYMD (or whatever format you want the date) in your program

Since end date is just the begin date + 12 months, you shouldn't need to prompt for the end date. In fact, since you're just using system variables, you shouldn't need to prompt for any dates.

Also, I would avoid using DEFINE to create fields to use in a WHERE clause. You already can determine the values using Dialogue Manager so defining new fields with those values just uses up resource unnecessarily. It also prevents the WHERE statement from being passed to the database engine. It would need to retrieve every record from the database, create the virtual field value for every record retrieved, and then throw out any that do not meet the criteria. Only use real fields in you selection criteria whenever possible.

Last item, since you brought up DEFINEs, is that you should be careful with field formats when using dates. Your example of END_DATE=&START_DATE + 12 could cause a problem. The variable value is just a number so if the date was 20120531, the defined value would be 20120543, which is not a valid date. It does not assume to know that the value you reference is a date just because you pull it from the system date. Make sure you use smart date formats (YYMD, MDYY, YYM, etc.) when working with dates as much as possible. I would also suggest sticking with the DATEADD function to always make sure you get what you're intending as opposed to just throwing some math function in there.

My guess is you're asking for a drop-down box with a list of months to choose from? If so, this is how I do it in JavaScript.

 function loadMonthData()
    {
     var today = new Date();
     var todaysMidnight = new Date(today.getFullYear(), today.getMonth(), today.getDate());
     var dateForLoop = new Date();
     dateForLoop = todaysMidnight;
 
     var selectBox = document.getElementById("timePeriodSelect");
     selectBox.innerHTML = '';
     for (var i=0; i<=23; i++)
       {
        // Get End Date
        var year = dateForLoop.getFullYear();
        var month = dateForLoop.getMonth() + 1;
        if (month == 13)
          {
           month = 1;
           year = year + 1;
          }
        var firstDayNextMonth = new Date(year, month, 1);
        var lastDayThisMonth = new Date(firstDayNextMonth.getFullYear(), firstDayNextMonth.getMonth(), firstDayNextMonth.getDate());
        lastDayThisMonth.setDate(lastDayThisMonth.getDate() - 1);
 
        if (month < 10) { var monthText = '0' + (month); }
          else { monthText = month; }
 
        // Set Dates
        selectBox.options[i]=new Option();
        selectBox.options[i].value = monthText + "/" + year;
        selectBox.options[i].endDate = monthText + "/" + lastDayThisMonth.getDate() + "/" + year;
        selectBox.options[i].text = monthText + "/" + year;
        selectBox.options[i].startDate = monthText + "/01/" + year;
 
        var dateForLoop = new Date(dateForLoop.getFullYear(), dateForLoop.getMonth(), 1);
        dateForLoop.setDate(dateForLoop.getDate() - 1);
       }
    }

This runs for 23 months. It loads a drop-down variable called timePeriodSelect which could be your start date, or your end date, or you could make one of each and load both.

It's a little complicated to look at but it shows year/month to the user and loads up month/day/year to the variable sent to FOCUS.

By the way, telling someone to use Search and then not provide them with results is the height of rudeness. If someone is struggling it's the last thing they need. If you can't help just don't post.

J.