Formula help String to Date

[Former Member]

Hi All,

I have a field that is supposed to be a date field however I think its just a normal string.

I am trying to workout how I can turn it into a date so I can use it in my Record selection formula, I had some ideas but I don't understand how to use the split function.

my date field is in the following format as a string DD-MMM-YYYY.

this is how I imagine the formula would go... but its wrong any ideas?

Split({Date Field}) = array

Date(array(1),array(2),array(3))

[Former Member · Accepted solution]

Hi Jason,

It says String [150] so 150 characters?

6 hour drive to go punch him... but I should have known these are custom user fields in the application I use so you can say if you want to enter a number string or date...

Alex

[Former Member · Accepted solution]

Yep. That means it's being stored as a character or text type field as opposed to a date/time field.

What kind of database are you using?

[Former Member · Accepted solution]

Its Microsoft SQL Server 2005 the application is .net

[Former Member · Accepted solution]

SQL Server isn't going to have a problem displaying that field as a DateTime data type.

If you are using a SQL Command as your data source use the CAST (or CONVERT) function to switch the data type.

SELECT 
all the other fields,
 CAST(AM_WORDERS_CUF_T.ATTRIBUTE34 AS DateTime) AS ATTRIBUTE34
FROM AM_WORDERS_CUF_T

If you built the report by adding tables and linking them in CR then I'd add a very simple VIEW to the database...

Something like this:

CREATE VIEW RealDate AS 
SELECT ID, CAST(ATTRIBUTE34 AS DateTime) AS ATTRIBUTE34 FROM AM_WORDERS_CUF_T

I don't know what the primary key name is on AM_WORDERS_CUF_T so I just useda gereris ID. be sure to change it to the PK name of the table before creating the view.

Then all you would have to do is add the view to your table list... Link it to the original table on the ID field and use the ATTRIBUTE34 from the RealDate view.

HTH,

Jason

[Former Member · Accepted solution]

Hi Jason,

Thank you very much for this, I got it working using the cast function in my SQL select.

I was not using a SQL command but ended up using one as I don't really want to bother the IT department here.

Kind Regards,

Alex

[Former Member]

Hi Marshall,

Thank you very much, I only had to change a few minor things..

LOCAL StringVar Array acMonths := ["JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"]; 
LOCAL StringVar cDateStr; 
LOCAL NumberVar nYear; 
LOCAL NumberVar nMonth; 
LOCAL NumberVar nDay; 
LOCAL NumberVar i; 
LOCAL DateVar dDate; 
 
cDateStr := UpperCase(Trim({AM_WORDERS_CUF_T.ATTRIBUTE34})); 
 
// added not equal to // Alex
IF Length(cDateStr) <> 11 THEN ( 
    nYear  := 1900; 
    nMonth := 01; 
    nDay   := 01; 
) ELSE ( 
 
    nYear    := ToNumber(Right(cDateStr,4));
    nDay     := ToNumber(Left(cDateStr,2)); 
 
    //convert three letter month abbreviation to month number 
    nMonth := -1; 
    i := 1; 

//Changed 12 to 13 as Dates in december were not being caught by the loop // Alex 
    While i < 13 AND nMonth < 0 DO ( 
        IF Mid(cDateStr,4,3) = acMonths<i> THEN ( 
            nMonth := i;   
        ); 
        i := i + 1; 
    ); 
 
    // If nMonth is not determined then default date to 1900/01/01 
    IF nMonth < 0 THEN ( 
        nYear  := 1901; 
        nMonth := 01; 
        nDay   := 01; 
    ); 
         
); 
dDate := Date( nYear, nMonth, nDay ); 
 
dDate;

[Former Member]

Hi Marshall,

I would be interested in getting a formula solution working, I wonder if there is a way I can still award you points.

I tried to get it myself but still stuck..

LOCAL StringVar Array acMonths := ["JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"];
LOCAL StringVar cDateStr;
LOCAL NumberVar nYear;
LOCAL NumberVar nMonth;
LOCAL NumberVar nDay;
LOCAL NumberVar i;
LOCAL DateVar dDate;
 
cDateStr := {AM_WORDERS_CUF_T.ATTRIBUTE34};
nYear    := ToNumber(Right(cDateStr,4));

//convert three letter month abbreviation to month number
nMonth := -1;
i := 1;
 
While i < 12 AND nMonth < 0 DO (
    IF Mid(cDateStr,4,3) = acMonths<i> THEN (
        nMonth := i;  
    );
    i := i + 1;
);
 
nDay     := ToNumber(Left(cDateStr,2));
If {AM_WORDERS_CUF_T.ATTRIBUTE34} = "" then dDate := Date(1900, 01, 01) else dDate := Date( nYear, nMonth, nDay );
dDate;

[Former Member]

Hi Alexander,

No problems. Let's give it a go.

I think the problem is with the date string field. We assumed that the field is consistently formatted and that every date string is valid which I don't think is actually true.

I would suggest placing both the date string and this formula side by side in the details section of your report to see which date strings converted and which ones defaulted to 1900/01/01.

 
LOCAL StringVar Array acMonths := ["JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"]; 
LOCAL StringVar cDateStr; 
LOCAL NumberVar nYear; 
LOCAL NumberVar nMonth; 
LOCAL NumberVar nDay; 
LOCAL NumberVar i; 
LOCAL DateVar dDate; 

cDateStr := Trim({AM_WORDERS_CUF_T.ATTRIBUTE34}); 

IF Length(cDateStr) <> 11 OR IsNull({AM_WORDERS_CUF_T.ATTRIBUTE34}) THEN ( 
    nYear  := 1900; 
    nMonth := 01; 
    nDay   := 01; 
) ELSE ( 

    nYear    := ToNumber(Right(cDateStr,4)); 
    nDay     := ToNumber(Left(cDateStr,2)); 

    //convert three letter month abbreviation to month number 
    nMonth := -1; 
    i := 1; 

    While i < 12 AND nMonth < 0 DO ( 
        IF Mid(cDateStr,4,3) = acMonths<i> THEN ( 
            nMonth := i;   
        ); 
        i := i + 1; 
    ); 

    // If nMonth is not determined then default date to 1900/01/01 
    IF nMonth < 0 THEN ( 
        nYear  := 1900; 
        nMonth := 01; 
        nDay   := 01; 
    ); 
         
); 
dDate := Date( nYear, nMonth, nDay ); 

dDate; 

If your database is case sensitive then add the UpperCase function when assigning to cDateStr

 
cDateStr := UpperCase( Trim({AM_WORDERS_CUF_T.ATTRIBUTE34}) ); 

Hopefully this version of the formula allows the report to complete. Let me know.

Regards,

Marshall

Edited by: Marshall Lin on Apr 9, 2010 5:52 AM

[Former Member]

Almost there I think I copied your formula exactly and it works, however if I use the database field for the variable cDateStr I get the following error message: "A month number must be between 1 and 12"

and it highlights this line of code.

dDate := Date( nYear, nMonth, nDay );

[Former Member]

Sorry, I did the formula in a hurry and I didn't take into account the possibility of not having a matching month i.e because the date string field is blank or the MMM used in the date string is not in the array of months etc.

I'm glad to see that you got it working using a SQL command.

Cheers

[Former Member]

The date format that your string is in is not a standard date format for Crystal. The year and day are not a probelm to convert. The month conversion to a numeric can be done using an array.

Here is a standalone forumla to demonstrate how to do your String to Date conversion. I broke down the formula to make it easier to read and understand.

1. Assign your date string field value to cDateStr variable.

2. Change the acMonths array elements to match the abbreviations used for months in the date strings to be converted.

LOCAL StringVar Array acMonths := ["JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"];
LOCAL StringVar cDateStr;
LOCAL NumberVar nYear;
LOCAL NumberVar nMonth;
LOCAL NumberVar nDay;
LOCAL NumberVar i;
LOCAL DateVar dDate;

cDateStr := "01-Mar-2010";
nYear    := ToNumber(Right(cDateStr,4));

//convert three letter month abbreviation to month number
nMonth := -1;
i := 1;

While i < 12 AND nMonth < 0 DO (
    IF Mid(cDateStr,4,3) = acMonths<i> THEN (
        nMonth := i;  
    );
    i := i + 1;
);

nDay     := ToNumber(Left(cDateStr,2));

dDate := Date( nYear, nMonth, nDay );
dDate;

I think this is what you are looking for.

Marshall

[Former Member]

Hi Guys,

Sorry for the slow reply I'm in Australia so odd timezone.

Thank you both for your help so far, but from the results I got when I tried the formulas it looks like I have given you misinformation.

Date({AM_WORDERS_CUF_T.ATTRIBUTE34})

I get the error "Bad date format error"

I also tried

stringvar foobar := {AM_WORDERS_CUF_T.ATTRIBUTE34};

datevar extractedDate := cdate(foobar);

extractedDate

But I get the error "Bad date format error" again

So I did a test formula to check and make sure its not already a date..

IsDate({AM_WORDERS_CUF_T.ATTRIBUTE34})

This returned TRUE.. however when I format this field I do not get the normal Date & Time tab which allows me to format the date, nor am I able to use this field to select records between 2 dates.

Sorry for sending you on a wild goose chase with the string theory.

any other ideas?

[Former Member]

In the Field Explorer right click the the Database Fields and make sure that Show Field Type is checked...

What Field Type is being shown to the right of AM_WORDERS_CUF_T.ATTRIBUTE34?

Then go punch whoever named that field...

Jason

[Former Member]

You're making it too complicated...

Date({DateField})

So if DateField = "07-Apr-2010"... formula = 4/7/2010

HTH,

Jason

[Former Member]

Also, the split function takes a delimiter as second parameter. If the parts of the string you want to extract are not delimited by spaces but other characters.

Like this

Split({DateField},"-"})

[Former Member]

You could use the CDate function. Works fine with your date format.

stringvar foobar := "2003-08-09";
datevar extractedDate := cdate(foobar);
extractedDate