Substring using modify stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
Substring using modify stage
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
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
Regards,
Sasikala V C
Sasikala V C
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
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
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
Regards,
Sasikala V C
Sasikala V C
-
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
If the format is YYYY-MM-DD then your mask should be "%yyyy-%mm-%dd"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 26
- Joined: Wed Jun 20, 2007 1:13 am
- Location: Chennai
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)
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.