Creating Time_Key from TimeStamp giving Error

mczern · Post by **mczern** » Wed Jul 19, 2006 12:04 am

I'm trying to create an Integer value representing the Time from TimeStamp without seconds. IE: Given a Timestamp of 2006-03-31 01:20:45 I need to create a TIME_KEY equal to 12000. The following works when there are time values > 00:00:00......

If IsNull( to_create_keys.ADMIT_DT) Then -1 Else ((StringToDecimal(TimeToString(TimestampToTime(to_create_keys.ADMIT_DT),'%hh%nn')))).
I did have a * 100 added on the decimal value, but DataStage generated an error for the 00:00:00 times so I do that step in the next tranform.

However, when I have a TimeStamp value with no Time component IE 2006-03-31. I get an error.... APT_CombinedOperatorController(15),2: Operator terminated abnormally: received signal SIGSEGV.

If I add a peek for the value I'm attempting to create I get the expected value of 0.... Peek_236,2: TIME_ADMIT:00:00:00 TIME_ADMIT_KEY:0 ADMIT_DT:2006-03-31 00:00:00

Any ideas on why I would get this error and how I can get this expression to succeed?

ray.wurlod · Post by **ray.wurlod** » Wed Jul 19, 2006 2:12 am

You may need to provide a format string for the timestamp. The default default for Timestamp is "%yyyy-%mm-%dd %hh:%nn:%ss" but your data seem to be "%mm/%dd/%yyyy %hh:%nn:%ss".

mczern · Post by **mczern** » Wed Jul 19, 2006 7:12 am

ray.wurlod wrote:You may need to provide a format string for the timestamp. The default default for Timestamp is "%yyyy-%mm-%dd %hh:%nn:%ss" but your data seem to be "%mm/%dd/%yyyy %hh:%nn:%ss".

Sorry... It was 2am when I posted. The format for the date is in the default format. I've corrected the initial post to make it clear.

One of the date values that is giving me an error is "2006-03-31".
In the peek it displays as "2006-03-31 00:00:00" So I don't think the format string is necessary.

As additional information. The algorithm I'm using works for 80% of the data that has a time component to the Timestamp. It's the other 20% that it fails on.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 19, 2006 7:52 am

Some examples of values where it succeeds and, particularly, where it fails may aid the diagnostic process! You should be able to capture these via a Peek stage connected to the Transformer stage's Otherwise/Log output.

mczern · Post by **mczern** » Wed Jul 19, 2006 9:45 am

ray.wurlod wrote:Some examples of values where it succeeds and, particularly, where it fails may aid the diagnostic process! You should be able to capture these via a Peek stage connected to the Transformer stage's Otherwise/Log output.

After much pain, I have figured out the cause of the SIGSEGV error.

When using the TimeStampToTime function all members of the time structure MUST be included in the format string! To get rid of the seconds one must then convert the Time to a String and Left substring the value.
The code for the solution follows:

If IsNull( to_create_keys.ADMIT_DT) Then -1 Else StringToDecimal(Left(TimeToString(TimeStampToTime(to_create_keys.ADMIT_DT),"%hh%nn%ss"), 4):"00")