DSXchange

Hello,

I'm trying to extract the time from a Timestamp column as an integer.

For example, for a timestamp like "2017-06-15 12:30:55", I want to output an integer equal to 1230.

I have mainly 2 issues I need to understand:

First, I tried using the following function: Instead of the correct result as a decimal, I get the following error :

APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%hh%nn" for conversion "decimal=decimal_from_timestamp[%yyyy%mm%dd%hh%nn%ss](timestamp)": APT_Conversion_Decimal_TimeStamp: Invalid Format [%hh%nn] used for string_from_date type conversion.

This to me seem strange as the following function gets me the correct result as a decimal:

TimeToDecimal(TimestampToTime(DSLink2.TIMESTAMP), "%hh%nn")

Second issue is converting that from a Decimal to an Integer. I just call AsInteger on the above function, but instead I get a warning and value is set to 0.

APT_CombinedOperatorController,0: Conversion error calling conversion routine decimal_from_time data may have been lost

Can someone help me understand what's going on?

Lapsusone,

It can be acheived by

substring

Stage variable=DSLink2.TIMESTAMP[12,8], --- o/p:12:30:55
after you get the time part, use HoursFromTime and MinutesFromTime,


concatenate these functions to get the desired results HoursFromTime:MinutesFromTime and store it in integer.

So what actually is your "workaround"?

As anudeepdasari001 mentioned, I used substring and it works this way. I still don't know why the functions I mentioned above don't work.

So, out of curiosity, is your "timestamp" field actually a string?

No, it's still a Timestamp column. I guess under the hood DataStage treats Timestamp fields as a string.

By the way from which source are you getting this timestamp field.
Could you brief us the design of the job.

pretty sure timestamps are numeric, but can be converted to strings on the fly due to the common need to do that.

To keep it simple, the job reads from a sequential file a timestamp in the default format and loads the date and time as separate fields in an Oracle database.

Is it in military time? Like 1 is 13:00 and midnight is 00:00 etc?

If so, a simple substring and e-replace ought to pull what you need from the string, and then convert that string back to integer.