Date conversion
Moderators: chulett, rschirm, roy
Date conversion
Hi,
In the source the format of date field is dd-mon-yy(ex : 29-apr-05).
I need to extract the month number from it .and apply some more functions on this field .(probably date functions) ,to apply date functions the format of the date must be dd-mm-yyyy. how to solve this.
In the source the format of date field is dd-mon-yy(ex : 29-apr-05).
I need to extract the month number from it .and apply some more functions on this field .(probably date functions) ,to apply date functions the format of the date must be dd-mm-yyyy. how to solve this.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
hi gbusson,gbusson wrote:no way to set the data type of your colmumn into "date"?
it will be easier!
how can I change the data type of a column when the data is present in the file ?
When I change the data type of date column in the column tab of sequential file stage, I am unable to view the data in the source file.
Re: Date conversion
Hi,balu124 wrote:Hi,
In the source the format of date field is dd-mon-yy(ex : 29-apr-05).
I need to extract the month number from it .and apply some more functions on this field .(probably date functions) ,to apply date functions the format of the date must be dd-mm-yyyy. how to solve this.
Use the ICONV and OCONV functions to get the date to your desired format and then apply the date functions what ever you want.
------
Rajesh
the problem is that Mon is not a valid format for PX.
ICONV and OCONV cannot be used in PX (unless using the basic tranformer or a server shared contanier) , since it is BASIC.
why don't you create a PX routine like this :
switch month :
case jan => 01
case feb =>02
Then you have the good format, you can transform into date.
ICONV and OCONV cannot be used in PX (unless using the basic tranformer or a server shared contanier) , since it is BASIC.
why don't you create a PX routine like this :
switch month :
case jan => 01
case feb =>02
Then you have the good format, you can transform into date.
I doubt a routine is necessary. Try the following in the transformer stage.
or its twin equivalent in modify stage.
Your output column should be defined as date.
Code: Select all
StringToDate(in.col, "%dd-%mmm-%yy)
Your output column should be defined as date.
Last edited by DSguru2B on Tue Nov 13, 2007 9:40 am, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: Date conversion
Not in a PX job.rsomiset wrote:Hi,
Use the ICONV and OCONV functions to get the date to your desired format and then apply the date functions what ever you want.
------
Rajesh
You may want to look at PX functions, probably date_from_string() or StringToDate().
Last edited by gateleys on Tue Nov 13, 2007 9:43 am, edited 1 time in total.
gateleys