How to convert this string 'm/d/yyyy h:mi' to date?

[j_nc_usa]

Hi,

I have a csv file that needs to be loaded into a DB2 table. The csv file has a date field in this format: 'm/d/yyyy hh:mi". Some examples are:

4/1/2008 1:23
12/31/2008 23:52
10/31/2008 22:20
..... etc

The month field is not always 2-digit. The date field is not always 2-digit. The hour field is not always 2-digit. Please see the examples above.

I would like to know the best way of loading this data into datastage. Use ICONV?? OR use any routines? OR use any transforms? Please help.

- Joy.

[danddmrs]

A routine with something like this should work.

InternalD = iconv(Arg1,"D4/MDY[2,2,4]")
InternalT = iconv(Arg2,"MT")
Ans = oconv(InternalD, "D4-YMD[4,2,2]"):' ':oconv(InternalT, "MT")

Guru's may have something better but I think this will get the job done.

Richard

[ray.wurlod]

You can do it in stage variables if you don't need to do a routine.

The Oconv on the time may require "MTS" as its second argument (rather than "MT") if the database requires seconds in its timestamp format.

[Tlam]

Hi,

I have the same date format mm/dd/yyyy h:mi. I have used following on the stage variable.

Oconv(Iconv(Date, "D/MDY[2,2,4] MT"), "D/MDY[2,2,4] MTS").

When I run the job, the job runs successful, but the data is not shown in the table. Please advice.

Thanks

[ray.wurlod]

Your conversions will not work. The second argument of each is invalid. You can not convert timestamps - you must split them in to date and time components, convert these separately, then re-assemble the result.

[Tlam]

Yes, you are right, my mistake. I will try to split it.

[chulett]

When I run the job, the job runs successful, but the data is not shown in the table. Please advice.

Not really all that successful then, eh? To me, this implies you are running the job from the Designer and haven't looked at the actual job log from the Director or you would undoubtedly see it chock full o' warnings.

[Tlam]

I have checked the log. No warning at all. I tried to debug it. Somehow it process the empty field. But thanks all. I have used the following method.

I use field to extract the date and then Iconv/Oconv and concatenated with '00:00:00'