Converting a varchar to datetime

[fayecart]

I am needing to convert a field on an ODBC database that is of datatype "varchar" which contains "date" information (ex: 06/02/2000 11:10).  I need to have this field converted to a date or datetime field in Crystal in order to subtract a given number of days for reporting purposes.  Is there a way to handle this in Crystal?  I'd greatly appreciate any information anyone can offer.  Thanks very much in advance.  Faye Carter

 

[Nilsen]

Here is the formula that I've sent to our users to use (as Crystal's data dictionaries converts date time fields to 22 character string fields)<br><br>If NumericText(Left({Cost Extract.Received Date Out},4) +<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mid({Cost Extract.Received Date Out},6,2) +<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mid({Cost Extract.Received Date Out},9,2) +<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mid({Cost Extract.Received Date Out},12,2) +<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mid({Cost Extract.Received Date Out},15,2) +<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Right({Cost Extract.Received Date Out},5))<br>Then DateTime(ToNumber(Left({Cost Extract.Received Date Out},4)),<br>&nbsp;&nbsp;&nbsp;&nbsp;ToNumber(Mid({Cost Extract.Received Date Out},6,2)),<br>&nbsp;&nbsp;&nbsp;&nbsp;ToNumber(Mid({Cost Extract.Received Date Out},9,2)),<br>&nbsp;&nbsp;&nbsp;&nbsp;ToNumber(Mid({Cost Extract.Received Date Out},12,2)),<br>&nbsp;&nbsp;&nbsp;&nbsp;ToNumber(Mid({Cost Extract.Received Date Out},15,2)),<br>&nbsp;&nbsp;&nbsp;&nbsp;ToNumber(Right({Cost Extract.Received Date Out},5)))<br>Else DateTime(1,1,1,0,0,0)<br><br><br><br>This formula will convert at string that is in the format:<br>&quot;MM/DD/YYYY HH:MM:SS.SS&quot;<br><br>For yours to work, you'll probably just want to calculate the date, as the a formula Date({datefield}) - X will return a date X number of days less than {datefield}<br><br><br>My soapbox:<br><br>I've complained to Crystal about their lack of formula functionality in their data dictionaries, and requested enhancements to them, but don't think they are in the works.&nbsp;&nbsp;They say we can use data dictionaries to &quot;Create complex data-manipulation formulas that users can access without the need to understand formula concepts.&quot;&nbsp;&nbsp;But we are restricted to the most basic formula's, thus the users have to write even more complex formula's than would have been required without the dictionary.

 

[DaveStL]

In version 8 you can use the CDateTime() function to accomplish this.