I am developing a report based on DB2 SQL. I pull in a datetime stamp and use the SQL function DATE() to pull just that out. I then set up a date parameter that checks aginst this field.

My problem is that this field comes out as YYMD. I can change that format on the report to MDYY, but cannot figure out how to change the variable so that I users can type in the date ranges as MM/DD/YYYY, and also display in this format in the report header.

Any help is greatly appreciated.

Thanks,

Kevin,

Have the users specify the date in the MM/DD/YYYY format then use Dialogue Manager to switch the pieces around to create the variable that you use to do the WHERE clause. Do you know anything about DM? Hints: EDIT function to extract the date pieces, concatenation to put them together in the correct order.

Also, in your profile signature, please specify your product suite, releases, and platform(s) so we may better help you.

I am familiar with the EDIT function, but not so much Dialogue Manager, especially when creating a report from SQL.

Would that go between the SQL and the report file?

MO Admin,

This is just another idea:

The simplest way to return a date in the format you desire is to use the built-in column function CHAR. Using this function you can convert a date column into any number of formats. The specific format you request, MM/DD/YYYY, is the USA date format. So, for example, to return the date in the format you requested for a column named START_DATE you would code the function as follows:

CHAR(START_DATE,USA)

OR

DATE(SUBSTR(START_DATE,5,2) || "/" || SUBSTR(START_DATE,7,2) || "/" || SUBSTR(START_DATE,1,4))


Hope this helps...


Tom

It is not the column that I am having a problem with. It is the &variable for the date range.

I want to type 05/01/2008 instead of 20080501. In the report heading, it shows the way it is typed in.

Kevin,

OK, Ginny gave you the answer, BUT, with only 7 posts, here's the code:

-SET &NEW_DATE = EDIT(&VARIABLE,'$$$$99/') || EDIT(&VARIABLE,'$$$$$$99/')|| EDIT(&VARIABLE,'9999');

OR

-SET &NEW_DATE = EDIT(&VARIABLE,'$$$$99/99/') || EDIT(&VARIABLE,'9999');

&VARIABLE is your incoming &NAME
&NEW_DATE now goes in the HEADING...

This message has been edited. Last edited by: Tom Flynn,

Thanks Tom and Ginny.

I used NEW_DATE for the where statement by converting MM/DD/YYYY into YYMD format by way of the EDIT in Dialogue Manager, and left the origional (&VARIABLE) as what is keyed in and what is displayed in the report header.

Thanks for you time.