ICONV Date / Time format mask

[MrBlack]

Can someone help me with format mask, I'm reading in a text file with data like

Code: Select all

11/16/13 9:30 PM MST

So far I've only used ICONV for just the date portion but I have never done a date/time.

I would expect a mask something like

Code: Select all

ICONV(DSLink1.Arg, "D/MDY HH:MM:SS AM MST")

I did find this website but haven't been able to make any combinations to work so far

http://docs.intersystems.com/cache20082 ... VBS_ficonv

[SURA]

You may get some hold by referring this thread.

viewtopic.php?t=135681&highlight=MM%2FDD%2FYY

[chulett]

You have to split the date from the time and convert them separately.

[ray.wurlod]

You have to split the date from the time and convert them separately. DataStage BASIC has no knowledge of timestamps in any form. And, in particular, there is no internal format of a timestamp in DataStage BASIC.

Curiously, perhaps, the supplied SDK Transforms and Routines do provide such a thing, though based on a zero point of 1970-01-01 00:00:00.

[chulett]

What's your goal here?

[bheaton_IHC]

Here's a template of what I've used before that will return it's output as a string in the Oracle To_Timestamp() format. You should be able to adapt this to suit your needs.

Code: Select all

Oconv(ICONV(Left(ExtractOvrdFile.EVENT_TIME_DTS,10),"D/MDY[2,2,4]"),"D-YMD[4,2,2]") : " " : Oconv(ICONV(Right(ExtractOvrdFile.EVENT_TIME_DTS,8), "MTS"),"MTS")

[MrBlack]

Thanks bheaton_ihc! That's a good example and I should be able to modify that for what I need to do.

[chulett]

It would have helped if you explained "what you need to do" in its entirety right off the bat rather then just ask about a part of the puzzle. At this point we still don't even know what that is.

[MrBlack]

chulett I did specify the boundries of the problem: Take a date time string and get it into a date-time data type in datastage. What I do with the data from there is my business and out of scope of the problem.

Now with the combined responses from ray.wurlord and bheaton_ihc the problem can be resolved. Thanks to ray we know that datastage/iconv can't handle date-time format masks and thanks to bheaton gives a solutions to separate the data into the two necessary elements for conversion into date-time data type.

Now if you have a more elegant solution I challenge you to post it, otherwise stop trolling that I didn't tell you my overall plan to rule the world when all I wanted was to get a text sting into a date-time data type.

[chulett]

Trolling? Get real.