Substring using modify stage

vcsasikala · Post by **vcsasikala** » Thu Mar 06, 2008 10:56 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Mar 06, 2008 11:41 pm

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.

Nripendra Chand · Post by **Nripendra Chand** » Thu Mar 06, 2008 11:45 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Mar 06, 2008 11:47 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Mar 06, 2008 11:49 pm

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

Nripendra Chand · Post by **Nripendra Chand** » Thu Mar 06, 2008 11:52 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Mar 06, 2008 11:55 pm

vcsasikala · Post by **vcsasikala** » Fri Mar 07, 2008 3:13 am

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

vcsasikala · Post by **vcsasikala** » Fri Mar 07, 2008 3:20 am

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?

ArndW · Post by **ArndW** » Fri Mar 07, 2008 3:24 am

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

vcsasikala · Post by **vcsasikala** » Fri Mar 07, 2008 3:28 am

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.

ArndW · Post by **ArndW** » Fri Mar 07, 2008 3:32 am

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.

vcsasikala · Post by **vcsasikala** » Fri Mar 07, 2008 3:37 am

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

ArndW · Post by **ArndW** » Fri Mar 07, 2008 3:40 am

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

Code: Select all

date3:string = string_from_date [%mm%dd%yyyy] (date2)

ray.wurlod · Post by **ray.wurlod** » Fri Mar 07, 2008 3:52 am

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.