How to convert 20070625 to Date Format(mm/dd/yyyy) in PX

[Shadab_Farooque]

I am having source data format as yyyymmdd which is of integer type(Eg:20070728).I have to convert it in to Date format and load in Oracle table.The column is "date" type in Oracle.

This is a Parallel Job.

Please advise

[us1aslam1us]

You can try stringtodate() in transformer stage or date_from_string() in the modify stage.

[thebird]

I am having source data format as yyyymmdd which is of integer type(Eg:20070728).I have to convert it in to Date format and load in Oracle table.The column is "date" type in Oracle.

Date datatype in Oracle is "TimeStamp" for DataStage.So you would have to convert your input interger value into a TimeStamp.

You can use the substring funtion - and then convert the formatted into a Timestamp using the StringToTimestamp function after appending a time component (say 00:00:00) to your date.

Aneesh

[DSguru2B]

Read the integer as character by specifying TO_CHAR() in the sql select and then in the transformer provide the following

Code: Select all

StringToDate(in.Date, "%yyyy%mm%dd")

[Shadab_Farooque]

I got the solution so I am marking this as resolved.

Thanks everyone for their suggestions.

I used the following in the transformer to convert date(20070718) to date type 07/18/2007.

StringToDate(DSLink3.Field005[5,2]:'/':DSLink3.Field005[7,2]:'/':DSLink3.Field005[1,4],"%mm/%dd/%yyyy").

Thanks

[kumar_s]

I guess you forgot to mark the post as mentioned.

[Shadab_Farooque]

I guess you forgot to mark the post as mentioned. ...

Thanks for the info.. Marking it as resolved