Bad date-format string

[Former Member]

Hi,

I have one field that is a string but it is actually a date with value Sep 8 2008 5:39PM. I would like to format this to 9/8/08. Looks simple but I tried the DateTimeValue function and it did not work. Example:

DateTimeValue ()

Can somebody help me on this?

Thanks!

[former_member292966 · Accepted solution]

Hi Marinette,

Unfortunately it's not quite that simple, especially because we starting from a string. The date functions require the year, month and day to be numeric values. To get to that point means you'll need to convert the date potion of your field to numeric values.

The month part is pretty simple by using something like:

NumberVar MyMonth;

select {@Field1}[1 to 3]

case "Jan" : MyMonth := 1

case "Feb" : MyMonth := 2

case "Mar" : MyMonth := 3

case "Apr" : MyMonth := 4

case "May" : MyMonth := 5

case "Jun" : MyMonth := 6

case "Jul" : MyMonth := 7

case "Aug" : MyMonth := 8

case "Sep" : MyMonth := 9

case "Oct" : MyMonth := 10

case "Nov" : MyMonth := 11

case "Dec" : MyMonth := 12;

This is assuming the months are the first 3 characters. The next problem is parsing out the days because they can be 1 or 2 characters. So I've used the Mid function to do this:

NumberVar MyDay;

MyDay := ToNumber (Mid ({@Field1}, 5, 2));

Now comes the hardest part, parsing out the year. Try this:

NumberVar MyYear;

Select Length (Mid ({@Field1}, 5, 7))

Case 7 : MyYear := ToNumber ({@Field1}[7 to 10])

Case 8 : MyYear := ToNumber ({@Field1}[8 to 11]);

So now you can do:

CDate (MyYear, MyMonth, MyDay);

Good luck,

Brian

[Former Member]

Try this

date(stringfieldvalue)

I think this works

Regards,

Raghavendra

[Former Member]

Create a formula with the following code and place on ur report.

Date("Sep 8 2008 5:39PM")

will return 9/8/2008

[Former Member]

Hi Marinette,

the crystal syntax cdate() function should handle your date string if you trim off the time value:

cdate(left({Your Field}, 10)

This pre-supposes that I can correctly count characters (haha) and that your month abbreviation is always 3 letters in length, and is a pretty common month abbreviation.