Skip to Content
author's profile photo Former Member
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)

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    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.

    BEFOREREADINGRECORDS;

    //

    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[1 TO 2]

    CASE '19':

    '0' & MINYEAR[3 TO 4] & MINMONTH & MINDAY

    CASE '20':

    '1' & MINYEAR[3 TO 4] & MINMONTH & MINDAY

    DEFAULT:

    '1' & MINYEAR[3 TO 4] & 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,'','')[1] = '1' THEN

    '20' & TOTEXT({DATABASE.DATE},0,'','')[2 TO 7]

    ELSE

    IF TOTEXT({DATABASE.DATE},0,'','')[1] = '0' THEN

    '19' & TOTEXT({DATABASE.DATE},0,'','')[2 TO 7];

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

    Test it and see if it works!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.