Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Bad date-format string

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!

replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question