silly but important

[muffntuf]

I know this is an easy one,but I can't remember for the life of me.

I wrote a command line in CRXI to handle a union join. I had to put the date fields to text (string) in the sql code to prevent errors. I need to now convert them back to date fields in CRXI. I tried using cstr but got an error, then cdate and got an error, what am I missing?

Thanks,
muffntuf

 

[synapsevampire]

Hard to say since you state that you used some unknown database to convert a date to some unknown format...

And you probably didn't NEED to convert them to strings to prvent erros, you could have converted them to a default date such as 1/1/1900 (which will still be the case of course if they are stored in strings that will produce erros as dates).

Here's the syntax:

// assumed format of MM/DD/YYYY
stringvar MyDate:= {table.stringdate};
cdate(val(mid(Mydate,7,4)),val(mid(Mydate,1,2)),val(mid(Mydate,4,2)))

Rearrange the mids to suit your date format.

You might also use a SQL Expression, which would prove faster, or if doing this in Crystal you might need the ISDATE function to check for what's stored in it now.


-k

 

[muffntuf]

I wondered if I didn't have to declare a variable. Thanks much, and yes I did all the usual suspects before finally having to convert to string.

Thanks again!

 

[synapsevampire]

You don't need to declare a variable, has nothing to do with the solution, it just helps people to understand the solution.

WHatever you're doing in Crystal, can be done more efficently on the database.

Again you state that you tried all of the suspects, but I think that you should instead be posting what you have (database type, example data and connectivity used) and what you need, you might discover that it's remotely possible that someone on earth has an idea you're unaware of.

Placing the conversion into a data view for instance will resolve for all processes going forward (reassinging your dba to copy person probably wouldn't hurt either, bad dates are indicative of poor database skills).

-k