Date conversion

[balu124]

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.

[gbusson]

hi,

some date functions exist in the transformer or the modify.

Read the manual.

I'd like to add that date functions are not very powerful in Datastage. Try to use the database instead.

[ray.wurlod]

What IS "the source"? What data type is your date column? The answers to those questions will help a more targeted response to your questions.

[balu124]

What IS "the source"? What data type is your date column? The answers to those questions will help a more targeted response to your questions. ...

hi ray,
The source is sequential file .data type of that column is varchar.

[gbusson]

no way to set the data type of your colmumn into "date"?

it will be easier!

[balu124]

no way to set the data type of your colmumn into "date"?

it will be easier!

hi gbusson,
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.

[rsomiset]

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.

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

[gbusson]

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.

[DSguru2B]

I doubt a routine is necessary. Try the following in the transformer stage.

Code: Select all

StringToDate(in.col, "%dd-%mmm-%yy)

or its twin equivalent in modify stage.
Your output column should be defined as date.

[gateleys]

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

Not in a PX job.

You may want to look at PX functions, probably date_from_string() or StringToDate().

[gateleys]

Go with DSGuru's solution.