Former Member

# Convert a number to a date and use that date to compare to two parameters

Post Author: jack7545

CA Forum: Formula

Crystal Reports XI - I would like to use the paramater field prompts to prompt for two date fields (range for the report). My problem is that my database saves all dates in a CYYMMDD numberic format (e.g. 0990101 = 19990101 and 1070101 = 20070101) - "old if year less then 40 logic for Y2K database). How can I conv ert the 7 digit number into a date field which can then be used to compare to the two date fields in my parameter (and what woudl the function look like that compars the converted number to the two date fields). I am very new to crystal (enough to be dangerous)

10|10000 characters needed characters exceeded

### Related questions

• Former Member
Posted on Jun 29, 2007 at 02:22 AM

Post Author: SKodidine

CA Forum: Formula

Here is a convoluted solution to your issue. I don't think there is a date function that will convert CYYMMDD to YYYYMMDD format (not that I am aware of).

1. I think it is easier to convert the two parameter dates into CYYMMDD format rather than the db field.

2. If you will be displaying the db date in your report, it is much easier to display it in YYYYMMDD format.

3. In the formula below, change the default value to either 0 or 1 as you deem fit.

Here goes:

1. Create a formula called Mindate and plug this into it. Replacing {?My Parameter} with yours.

//

STRINGVAR MINYEAR := TOTEXT(YEAR( MINIMUM({?MY PARAMETER})),0,'','');

STRINGVAR MINMONTH := TOTEXT(MONTH( MINIMUM({?MY PARAMETER})),0,'','');

STRINGVAR MINDAY := TOTEXT(DAY( MINIMUM({?MY PARAMETER})),0,'','');

//

IF LENGTH(MINMONTH) = 1 THEN MINMONTH := '0' & MINMONTH;

IF LENGTH(MINDAY) = 1 THEN MINDAY := '0' & MINDAY;

//

STRINGVAR MINCYYMMDD := SELECT MINYEAR&#91;1 TO 2&#93;

CASE '19':

'0' & MINYEAR&#91;3 TO 4&#93; & MINMONTH & MINDAY

CASE '20':

'1' & MINYEAR&#91;3 TO 4&#93; & MINMONTH & MINDAY

DEFAULT:

'1' & MINYEAR&#91;3 TO 4&#93; & MINMONTH & MINDAY ;

//

MINCYYMMDD;

2. Create another formula called Maxdate and copy the contents from above and change MIN/MINIMUM to MAX/MAXIMUM.

3. The result of these two formulae will be two strings in the CYYMMDD format. Change them to numeric.

4. In your record selection criteria put the following statement;

{DATABASE.DATE} IN {@MINDATE} TO {@MAXDATE}

5. If you will be displaying the database date in your report, then after placing it on the report, right click and then on 'format field' and then on the 'Common' tab. Across from 'Display String' click on the button marked x-2. In the formula workshop window type:

IF TOTEXT({DATABASE.DATE},0,'','')&#91;1&#93; = '1' THEN

'20' & TOTEXT({DATABASE.DATE},0,'','')&#91;2 TO 7&#93;

ELSE

IF TOTEXT({DATABASE.DATE},0,'','')&#91;1&#93; = '0' THEN

'19' & TOTEXT({DATABASE.DATE},0,'','')&#91;2 TO 7&#93;;

This will display the database date in YYYYMMDD format. Reformat it as desired.

Test it and see if it works!

10|10000 characters needed characters exceeded
• Former Member
Posted on Jun 30, 2007 at 10:10 PM

Post Author: synapsevampire

CA Forum: Formula

Skodidines solution couldn't pass anything to the database, so instead try a SQL Expression to return a proper date field from the database and reference that in the record selection formula, or use a Command Object and nest the conversion to a date within that. Basically any database supports doing this.

Of course I'd suggest shuffling your dba out the back door for allowing this sort of storage, and it would be best to direct them through a huge paper shredder so as not to inflict their logic on any other company ðŸ˜‰

Of course this is dependent upon the database being used, which you didn't bother to post...

Try including technical information in each post.

-k