DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
Lapsusone
Participant



Joined: 15 Jun 2017
Posts: 4

Points: 29

Post Posted: Thu Jun 15, 2017 10:39 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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:
Code:
TimestampToDecimal(DSLink2.TIMESTAMP, "%hh%nn")


Instead of the correct result as a decimal, I get the following error :
Quote:
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:
Quote:
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.
Quote:
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?
anudeepdasari001
Participant



Joined: 14 Dec 2015
Posts: 42
Location: Arizona
Points: 354

Post Posted: Thu Jun 15, 2017 11:59 am Reply with quote    Back to top    

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.

_________________
anudpETL
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Thu Jun 15, 2017 9:09 pm Reply with quote    Back to top    

So what actually is your "workaround"?

_________________
-craig

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Lapsusone
Participant



Joined: 15 Jun 2017
Posts: 4

Points: 29

Post Posted: Fri Jun 16, 2017 8:38 am Reply with quote    Back to top    

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.

Code:
EREPLACE(DSLink2.TIMESTAMP[12,8],":","")[1,4]
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Fri Jun 16, 2017 9:02 am Reply with quote    Back to top    

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

_________________
-craig

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Not yet rated
Lapsusone
Participant



Joined: 15 Jun 2017
Posts: 4

Points: 29

Post Posted: Fri Jun 16, 2017 9:15 am Reply with quote    Back to top    

No, it's still a Timestamp column. I guess under the hood DataStage treats Timestamp fields as a string.
Rate this response:  
Not yet rated
anudeepdasari001
Participant



Joined: 14 Dec 2015
Posts: 42
Location: Arizona
Points: 354

Post Posted: Fri Jun 16, 2017 10:05 am Reply with quote    Back to top    

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

_________________
anudpETL
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 206

Points: 2170

Post Posted: Fri Jun 16, 2017 12:33 pm Reply with quote    Back to top    

pretty sure timestamps are numeric, but can be converted to strings on the fly due to the common need to do that.
Rate this response:  
Not yet rated
Lapsusone
Participant



Joined: 15 Jun 2017
Posts: 4

Points: 29

Post Posted: Fri Jun 16, 2017 12:45 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 206

Points: 2170

Post Posted: Fri Jun 16, 2017 12:55 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours