Modify Stage Question - Multiple conversions

[Havoc]

Hi,

One of the tables has an input column of Decimal( 8 ) which contains values:

20070112
20060513

which are in the yyyymmdd format

I was thinking of using a modify stage to do this conversion but how can i write a single specification with two datatype conversions. I know you cant write two specifications on one source column.

I believe we will have to convert the decimal to a string using string_from_decimal and then use date_from_string [%yyyy%mm%dd]

But can I do this in one specification?

[JoshGeorge]

Is the input column really decimal? You didn't specify any scale in the data type specification nor in the examples/values you posted. If they are expected as date values they shouldn't be expected as decimal at all. That will be:
20070112.00...
20060513.00...
If input is coming without sacle values, did you try to avoid the string from decimal function call and do the later only.

[aliasZero]

You can perform only one specification on one source column at a time in modify stage. Try using 2 modify stages

[ray.wurlod]

It is documented that two adjacent Modify stages is not supported. Place a Copy stage between them if you need two.

[Havoc]

It is documented that two adjacent Modify stages is not supported. Place a Copy stage between them if you need two. ...

Yes, that's right ...
Josh, yes there is no scale specified but as you mentioned it does come in as 20070112.0

Explanation of the problem's given below

when there is a string_from_decimal .. the resultant string has a decimal point appended to it at the end....

For eg:

12345 - decimal( 8 )
'12345.' - varchar(10)

thus the resulting date_from_string conversion in the next modify stage (following the copy) throws an unhandled exception ...

Is there a way i can get rid of that decimal point in the modify stage?

[shamshad]

There might be just 1 function but below is the logic that should work within a TRANSFORMER stage

Let's say data comes as "20070112.0"

(1) Step1: Get everything before decimal
(2) Step2: Data is now in YYYY-MM-DD format. So use StringtoDate()

How to do that:-

(1) Find position of decimal in string (ex 9 here)
(2) Get only portion of string before decimal string[1,(index position-1)]
which is string[1,8] (9-1)=8
(3) Use StringtoDate(String,[%yyyy-%mm-%dd]) to get the desired date

Hope it helps you.

[Havoc]

There might be just 1 function but below is the logic that should work within a TRANSFORMER stage

Let's say data comes as "20070112.0"

(1) Step1: Get everything before decimal
(2) Step2: Data is now in YYYY-MM-DD format. So use StringtoDate()

How to do that:-

(1) Find position of decimal in string (ex 9 here)
(2) Get only portion of string before decimal string[1,(index position-1)]
which is string[1,8] (9-1)=8
(3) Use StringtoDate(String,[%yyyy-%mm-%dd]) to get the desired date

Hope it helps you.

Yes, doing this in a Transformer would be pretty easy. As I mentioned, since there is no scale for the input decimal column ... you could use the AsInteger() function for the decimal column and directly place that as the derivation for you StringToDate() function. But, I was wondering how we can achieve this in the modify stage.

[JoshGeorge]

Just change the data type of your Input Column from decimal to integer regardless of what the real input type is and do

Code: Select all

outputCol:date = date_from_string [%yyyy%mm%dd] (InputCol)  

in your modify stage.