Date conversion from mm/dd/yy to CCYY-MM-DD

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Titto
Participant
Posts: 148
Joined: Tue Jun 21, 2005 7:49 am

Date conversion from mm/dd/yy to CCYY-MM-DD

Post by Titto »

How to convert input MM/DD/YY date value (char) to CCYY-MM-DD (Date type) of target table.

I am using

Code: Select all

 ICONV (READ.DATE, "D-YMD[4,2,2]")
but it is not converting.

Any help appriciated!
hrthomson
Participant
Posts: 25
Joined: Fri Aug 20, 2004 9:57 am
Location: Ottawa, Canada

Post by hrthomson »

There may be an easier way, but the following works:

OCONV(DateIconv(READ.DATE),"D-YMD[4,2,2]")


DateIconv routine should be included in DS.

I hope it helps!

Heather
what a man is contributes much more to his happiness than what he has, or how he is regarded by others.
Titto
Participant
Posts: 148
Joined: Tue Jun 21, 2005 7:49 am

Post by Titto »

Hi Heather,
where do i get DateIconv ? is it a routine?

Thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Converting dates from one output format to another requires two steps - first into internal format and then back out in the desired external format:

Code: Select all

OCONV(ICONV(READ.DATE, "D/MDY"), "D-YMD[4,2,2]")
You only took the first input step - but you used the date mask needed for the output step.

DateIconv must be a custom Heather routine or transform. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
pnchowdary
Participant
Posts: 232
Joined: Sat May 07, 2005 2:49 pm
Location: USA

Post by pnchowdary »

Hi Titto,

You can use the below transformation and tell me whether it works for you.

Code: Select all

Oconv(Iconv(Read.Date,"D2/"),"D-YMD[4,2,2]") 
Heather,

As far as I know, there is no standard function in Datastage called DateIconv, if it is, please let me know where I can find it. I am assuming that it might be some custom transform that you guys wrote yourself.
Thanks,
Naveen
Titto
Participant
Posts: 148
Joined: Tue Jun 21, 2005 7:49 am

Post by Titto »

Thanks all it worked out!!
as per Craig and Chowdary advice..
hrthomson
Participant
Posts: 25
Joined: Fri Aug 20, 2004 9:57 am
Location: Ottawa, Canada

Post by hrthomson »

Sorry guys, maybe it is. It was authored by a name I didn't recognize, but maybe another consultant imported it while on contract. It's simple really - parses the input for the format and spits it out as an Iconv. Nothing to it. The last posting is much simpler!

cheers,

Heather
what a man is contributes much more to his happiness than what he has, or how he is regarded by others.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Suggestion to all employers of consultants: make sure (a condition of hire?) that anything the consultant leaves is tagged with the consultant's contact details, and some kind of warranty as to fitness for purpose (plus, maybe, some form of support undertaking).

You won't regret it.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Suggestion to all consultants of employers: include something like this in all your work.
This code is provided "AS IS" without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
:wink:

(just joking Ray)
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply