Char to Timestamp
Moderators: chulett, rschirm, roy
Char to Timestamp
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 !
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 !
Re: Char to Timestamp
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.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Code: Select all
NEWCOL:timestamp= timestamp_from_string[%yyyy%mm%dd](OUTCOL)
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
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?
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?
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.
![Sad :(](./images/smilies/icon_sad.gif)
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.
Try the following
Code: Select all
StringToTimestamp(TRIM(INPUTCOL):"000000","%yyyy%mm%dd%hh%nn%ss")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.