DSXchange

Hi,
I'm having date column in YYYYMMDD format. This is Varchar(8). I need to convert it to MMDDYYYY in modify stage. I don't want to use transformer. what is the specification do I need to give?

Is there any other way to do this without using transformer?


Thanks,
Sasikala

There is no way within DataStage to accomplish this task without a Transformer or Build stage. If the data are coming from a file you might use a Filter command, if coming from a database you might use an SQL expression. But these are not what I include in "within DataStage" techniques.

use the following derivation in Modify stage:
Op_Column:date = date_from_string [%yyyy%mm%dd] (In_Column)

the output column will have date in default format defined at job level. If you want output date to be in 'MMDDYYYY' then change the date format at job level.

Thanks,
Nripendra

But it was stated that the source column is of type Date. Therefore you can not use date_from_string().

But it was stated that the source column is of type Date. Therefore you can not use date_from_string().

hi Ray,

Sasikala mentioned that date column which is varchar. so i thought the source col is of varchar type which contains date.

Thanks,
Nripendra

Thanks for reply Nripendra and Ray.


I tried this but I didn't get the output.
I gave specification : date2:date = date_from_string [%yyyy%mm%dd] (date1)

date1 in my input column varchar 8.
date2 is output column as datatype DATE.

there is no output from modify stage. Also job is not aborting and no warnings.

seq file ---green---> modify ---black---> dataset

Hi,
I'm getting output values. I have changed the default format to %mm%dd%yyyy.
But output is still in the format YYYY-MM-DD.
I don't know why?

If the format is YYYY-MM-DD then your mask should be "%yyyy-%mm-%dd"

My output should be in MMDDYYYY format. But I'm getting YYYY-MM-DD.

I have changed my default to %mm%dd%yyyy.

Do I need to change any other thing.

Sorry, I just wasn't paying attention to the first post and format there. Ok, so in your modify stage you have now successfully converted the string to a date. Now you need a 2nd modify stage which will convert this formatless date into a char datatype with appropriate formatting.

ok. I'm new to modify satge. can you please tell me how to get my desired format.

Please read the docs, the function is the opposite of the one you just used to generate the date.

All is well. The date format you get when you view a date in the View Data browser is governed by the default date format string currently in force. The date itself, once it's been converted, is a binary form.