Page 1 of 1
How to convert 20070625 to Date Format(mm/dd/yyyy) in PX
Posted: Tue Jul 24, 2007 7:20 am
by 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
Posted: Tue Jul 24, 2007 11:05 am
by us1aslam1us
You can try stringtodate() in transformer stage or date_from_string() in the modify stage.
Re: How to convert 20070625 to Date Format(mm/dd/yyyy) in PX
Posted: Tue Jul 24, 2007 12:34 pm
by thebird
Shadab_Farooque wrote: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
Posted: Tue Jul 24, 2007 1:43 pm
by 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")
Posted: Wed Jul 25, 2007 2:42 am
by 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
Posted: Wed Jul 25, 2007 3:34 am
by kumar_s
I guess you forgot to mark the post as mentioned.
Posted: Wed Jul 25, 2007 4:46 am
by Shadab_Farooque
kumar_s wrote:I guess you forgot to mark the post as mentioned.
...
Thanks for the info.. Marking it as resolved