Help converting a character field to a date field

[Jonesxx]

Hi,

I am using Crystal version 8.5. In the database we are using a character field to hold date information. In SQL the data appears as DD/MM/YY e.g. ‘28/12/07’ this is how the data appears in SQL. As this field is a character field does anybody know how to false Crystal to make it think it is a date field as I need users to filter the report based on the data in this field.

Also I have another character field which is being used in the database to enter date information, in SQL the data appears as DD/MM/YY 00:00:00, again I need to convert this into date format in Crystal so that users can filter the report based on this field.

I’m not sure of the syntax for either of the above, I’d be grateful for any assistance you could give.

Thanks

 

[lbass]

The following formula should work for both instances:

date(2000+val(mid({table.stringdate},7,2)), val(mid({table.stringdate},4,2)),val(left({table.stringdate},2)))

-LB

 

[Jonesxx]

Hi Ibass,

Thanks for this, however after writing the formula I keep getting the following error: A month must be between 1 and 12.

Any ideas?

 

[dgillz]

Please show sample data for months 1 thru 9 and days 1 thru 9. Do they have a leading zero?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

Please show samples of the field as Don suggested, and also post the exact formula you used.

-LB