Date Conversion

pavans · Post by **pavans** » Fri Apr 10, 2009 5:39 am

HI All,

I have a date field coming from source file which has values like:

2/28/2009 8:03 PM Mchen
2/28/2009 8:03 PM Philip Law

My target is a DB2 Database which is TimeStamp value.

I have searched the forum.
Tried IsValid, Left, Right, Field, StringToTimeStamp but unable to convert the value to a Timestamp.
Any help is really appreciated.

chulett · Post by **chulett** » Fri Apr 10, 2009 7:06 am

What exactly did you try? Post your StringToTimestamp attempt syntax and we'll go from there.

ArndW · Post by **ArndW** » Fri Apr 10, 2009 8:23 am

Note that "Philip Law" and "Mchen" are not part of any date system that I know of, so you would need to remove that part of the string prior to conversion.

chulett · Post by **chulett** » Fri Apr 10, 2009 8:51 am

Yup, and you typically need to standardize the dates so month/day are forced to two digits and also handle the conversion of AM/PM to military time. Typically.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 10, 2009 10:17 am

If not military time ("zulu"), then at least 24-hour clock, ideally using ISO 8601 standards (YYYY-MM-DD HH:MM:SS). You only get one format mask, but days and months can have one or two digits in them. That's why you need to force them to two digits by adding leading zeroes. Try to construct a timestamp string, using substring and concatenation techniques, that matches your default timestamp format; that's the easiest approach.

chulett · Post by **chulett** » Fri Apr 10, 2009 11:10 am

Sorry, yes meant time on a 24-hour clock not strictly "zulu" or GMT time.