Converting string DDMMMYYYY to Date format

[tekniks]

I'm using CR 8.5 but in the formula window I don't see a direct conversion from the string type ddmmmyyyy(for ex. 01Mar04) to Date format.
I also want that the selection to pass through the server after converting to date format. The records in the database are in date-time format.

Is there any easy approach to this?

TKS

Teks

 

[dgillz]

I do not believe there is a "direct" conversion, you would have to write something for this. I would bet the farm that this would not pass to the server for selection purposes.

Here's the formula I would use:

Date(2000+tonumber(Right({YourField},2)),
(if mid({YourField,3,3)="Jan" then 1 else
if mid({YourField,3,3)="Feb" then 2 else
if mid({YourField,3,3)="Mar" then 3 else
if mid({YourField,3,3)="Apr" then 4 else
if mid({YourField,3,3)="May" then 5 else
if mid({YourField,3,3)="Jun" then 6 else
if mid({YourField,3,3)="Jul" then 7 else
if mid({YourField,3,3)="Aug" then 8 else
if mid({YourField,3,3)="Sep" then 9 else
if mid({YourField,3,3)="Oct" then 10 else
if mid({YourField,3,3)="Nov" then 11 else
if mid({YourField,3,3)="Dec" then 12),
tonumber(left({yourfield},2)))

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

http://www.trianglepartners.com

 

[sommererdbeere]

i'm not sure if i understand this or not, but if you wanna convert string to date, isn't you will use this:

cstr({table_date_name}, "yyyy"))

this will give you the year. if you want mmddyyyy, i used this before:

cdate(cstr(DatePart ("m",(table_date_name}, 0)& "/" &
cstr((table_date_name}, "d")& "/" &
cstr((table_date_name}, "yyyy"))

hope this help

 

[chelseatech]

I would use
CDate(picture({table.field},"xx-xxx-xxxx"))

Add the punctuation and then let CDate do all the hard work.

You might like to check it's a valid date with..
If IsDate(picture({table.field},"xx-xxx-xxxx")) then...

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[sommererdbeere]

when i convert to date, i use

to_char({tablename},'YYYYMMDD')

i hope this help...

m

 

[kskid]

I think you meant to_date and not to_char when reading the string from the database. If that is the case, wouldn't an SQL Expression work here?

//@FieldDate
to_date(table.field,'YYYYMMDD')

Then format @FieldDate to whatever date you want