Char to Timestamp

[rockr]

Hi everyone,

I have a job where I get the input as char and I have the data in this format "19500101".

I used the modify stage to convert this column to timestamp, I used the specification as "NEWCOL:timestamp = timestamp_from_string (INPUTCOL)" . I get the output as "NULL" in the target dataset. Am I doing something worng here ?.

I need the output as timestamp to write to a Oracle stage. is there anyway I can convert it ?

Thanks for reading !

[sud]

You are getting null since the input is not in default format i.e., "%yyyy-%mm-%dd hh:nn:ss". Put a format string and it should work.

[DSguru2B]

You are not giving the mask of the input format. Else the engine will not know what is the format date that you are trying to convert.

[rockr]

(NEWCOL:timestamp)= timestamp_from_string(OUTCOL,%yyyy%mm%dd) is this correct ?

[sud]

Code: Select all

NEWCOL:timestamp= timestamp_from_string[%yyyy%mm%dd](OUTCOL)

I wonder if you need an actual time component in the input, if this does not work, try converting to date and then date to timestamp.

[rockr]

It dose not work, Can I convert Char to dat and Date to timestamp as you said in a single modify stage ?

[ArndW]

The Modify stage allows only one action to be done per column.

[rockr]

How do I use
StringToTimestamp(%string%,[%"%yyyy-%mm-%dd %hh:%nn:%ss"%]) Function in the transformer ?


The part in the RED is supposed to the format of the input string or the output string. I suppose its the INPUT format, So if my format is "20010101" am i supposed to use "%yyyy%mm%dd"

[DSguru2B]

Correct.

[rockr]

ok,

I am using StringToTimestamp(INPUTCOL,"yyyymmdd") and my input is 20070101

This is my FATAL ERROR log

APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "yyyymmdd" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid Format [yyyymmdd] used for string_from_time type conversion.


Please point out if I might have done something wrong?

[DSguru2B]

yyyymmdd is not the same as %yyyy%mm%dd

[chulett]

What happened to the %s?

[rockr]

Same ERROR when I use "%yyyy%mm%dd"

APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%yyyy%mm%dd" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid Format [%yyyy%mm%dd] used for string_from_time type conversion.

[DSguru2B]

Try the following

Code: Select all

StringToTimestamp(TRIM(INPUTCOL):"000000","%yyyy%mm%dd%hh%nn%ss") 

[rockr]

But my input data is "20010101", shouldnt I mention the mask for the Input data ?