Convert String to DateValue

[rhper14]

I have a field {sortdate} that is actually a date but is formatted in the database as a string. So the text coming into Crystal (8.5)looks like "M/D/YYYY", including the slashes. The Date formula needs the string to be "YYYY,MM,DD" to work, right? The only way I can think of to convert this back to a datevalue is to use an instr formula looking for the first slash and an instrrev to look for the second slash, and then left, mid, and right formulae to identify the M, D, and Y.

numbervar pos: = instr ({sortdate}, "/")
numbervar pos2 := instrrev ({sortdate}, "/")
stringvar m := left ({sortdate},pos-1)
stringvar d := mid ({sortdate},pos+1, pos2-1)
stringvar y := right ({sortdate}, pos2+1)
datevar actualdate := (y,m,d)

There's got to be an easier way to do this. Anyone know of an easier/better/cleaner way of doing this? Thanks

 

[kskid]

I'm thinking you can use

sortdate := cdate({table.sortdate})

but don't have Crystal available to test.

-LW

 

[synapsevampire]

As with any software requirement, that depends upon your version of the software, which you didn't post.

Keep in mind that you have a string field, which requires conversion, so expect to have to go through hoops to overcome the database developers shortcomings

A fun way is to use the SPLIT function, as in:

stringvar m := val(split({sortdate},"/")[1]);
stringvar d := val(split({sortdate},"/")[2]);
stringvar y := val(split({sortdate},"/")[3]);
cdate(y,m,d)

or in a lengthy single statement:

cdate(val(split({sortdate},"/")[3]),val(split({sortdate},"/")[1]),val(split({sortdate},"/")[2]))

Also consider creating a SQL Expression which would prove faster, or better yet, create a View on the database which provides a real date by casting/converting the field.

After you've ran the dba through the paper shredder of course.

-k