Formula needed converting string to date field

[Jonesxx]

Hello,

I am using Crystal reports version 8.5. I am using the current formula to convert a string field into a date field:

Datevalue(tonumber(mid({table.field},1,4)),tonumber(mid({table.field},5,2)),tonumber(mid({table.field},7,2)))

I would also like the formula to display a default date where there is a null value any ideas?

Thanks

 

[synapsevampire]

Try:

if isnull({table.date})
or
trim({table.date}) = "" then
cdate(1900,1,1)
else
cdate(val(left({table.field},4)),val(mid({table.field},5,2)),val(mid({table.field},7,2)))

Replace 1900,1,1 with whatever default date you'd like.

-k

 

[dgillz]

Assumming your field is number in a YYYYMMDD format:

NumberToDate({YourField})

If your field is a text field you will have to convert it to a number first:

NumberToDate(Val({YourField}))

NumberToDate is found under additional functions, not date functions. I forget when this became a part of crystal, you used to have to download the UFL. So if you do not find it under additional functions, search for the UFL on the BO website.


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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown