Date conversion

Willo · Post by **Willo** » Wed Feb 04, 2004 10:59 am

Hi,

I'm having a problem with dates, I have a date in the format of:

yyyy-mm-dd 00:00:00

I need to convert it into the follwoing format:

dd/mm/yyyy 00:00:00

Can anyone help me?? These date conversions are driving me crazy!!!

Thanks

Willo

chulett · Post by **chulett** » Wed Feb 04, 2004 11:12 am

Iconv and Oconv are your friends and are usually used together. If you search in this forum for them, you'll see there are a ton of posts discussing date formatting issues and resolutions.

Code: Select all

Oconv(Iconv(field_name,"D-YMD[4,2,2]"),"D/DMY[2,2,4]"

This should get you started but will need to be modified to handle the time portion. The Status function can be used to check for success of either conversion.

You can also check out the Date routines in your 'sdk' folder for examples of using these functions.

Willo · Post by **Willo** » Wed Feb 04, 2004 11:51 am

chulett,

Thanks for your reply, the timestamp is real issue I'm having, I can convert yyyy-mm-dd but I don't know what to do about the timestamp element.

I've looked at the SDK but it only converts to internal and not extrenal.

chulett · Post by **chulett** » Wed Feb 04, 2004 12:12 pm

Well, if the time portion is always zero you could cheat.

Convert the Date and then append a zero time string at the end of it.

If you want to do it right, use the FIELD command to break the string up into its Date and Time portions. Convert the Date and then stick the time back on the end afterwards. Make this reusable by writing a Routine to do this.

That help?

davidthree · Post by **davidthree** » Wed Feb 04, 2004 12:58 pm

As an alternative to using the inner/outer conversion functions, why not just process the date using string manipulation:

Code: Select all

MyDate[9,2]:'/':MyDate[6,2]:'/':MyDate[1,4]:MyDate[11,9]

should perform the conversion you require.

Although this might be slow (?)

David

jreddy · Post by **jreddy** » Wed Feb 04, 2004 1:22 pm

Hi,

I guess this is pretty long piece of derivation, but it gets the job done..

Oconv(Iconv(field(<linkname.columnname>, " ", 1, 1), "D-YMD[4,2,2]"), "D/DMY[2,2,4]"):" ":field(<linkname.columnname>, " ", 2, 3)

if you need to get it written into oracle table, then i would suggest you prefix this with
DateTimeStampToOraOCIWithTime(DateGenericToTimeStamp(<piece of code shown above>))

Hope this helps you..it worked for me

Willo · Post by **Willo** » Thu Feb 05, 2004 3:07 am

Cheers guys that code works, I was tempted by chulett's suggestion of trimming the timestamp but that would be cheating!!

ray.wurlod · Post by **ray.wurlod** » Thu Feb 05, 2004 3:01 pm

Willo wrote:Cheers guys that code works, I was tempted by chulett's suggestion of trimming the timestamp but that would be cheating!!

It's not cheating, since DataStage is a "data type free environment".
And it may be quicker!