Skip to Content
author's profile photo Former Member
Former Member

Converting String date to Crystal Date format

Post Author: bsimpsonky

CA Forum: Formula

I have a SQL database that stores dates in mmddyyy string format. I need to convert this string format into a crystal date format so that it can be properly evaluated with crystal date range parameters.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 01:09 PM

    Post Author: bettername

    CA Forum: Formula

    stringvar mydate:=;

    //get the inidivual dateparts from the string

    numbervar the_day:=tonumber(left(mydate,2));numbervar the_month:=tonumber(mid(mydate, 3,2));numbervar the_year:=tonumber(right(mydate, 4));

    //convert the dateparts to a date

    date(the_year,the_month,the_day);

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 02:52 PM

    Post Author: bsimpsonky

    CA Forum: Formula

    Thanks for the speedy reply. I'm sure this will work, but I was in error about the string format. It is actually stored as mm/dd/yyy, so how would that date conversion be accomplished?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 02:59 PM

    Post Author: bettername

    CA Forum: Formula

    Does it have slashes in? Not that it matters, this'll take them out...

    //remove the slashes (if they exists) from the date string and assign to a variable since I can't be bother to type out the field name each timestringvar mydate:=replace(,"/","");

    //get the individual dateparts from the string and assign to number variable (lookup the date() function to see why)numbervar the_day:=tonumber(mid(mydate, 3,2)); //day = the middle 3rd and 4th characters from the stringnumbervar the_month:=tonumber(left(mydate,2)); //month = 1st and 2nd characters from the stringnumbervar the_year:=tonumber(right(mydate, 4));//year = last 4 characters from the string

    //convert the dateparts to a date date(the_year,the_month,the_day);

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 04:43 PM

    Post Author: bsimpsonky

    CA Forum: Formula

    This took care of my problem. Thanks!!!!!!

    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.