Page 1 of 1
warning with string_from_timestamp function
Posted: Wed Oct 25, 2006 2:15 am
by rameshrr3
I get the following warning when using string_from_timestamp function in Modify Stage
Input COlumn is "dt" of type timestamp
Output is "dt_1" of type VarChar
Specification is
dt_1:string = string_from_timestamp(dt)
Where do i specify a format string?Documentation is not really helpful
Sequential_File_0: When checking operator: When binding input interface field "sophis_changed_dt1" to field "sophis_changed_dt1": Implicit conversion from source type "string" to result type "string[max=23]": Possible truncation of variable length string.
Thanks
Ramesh
Update: Suppressed warning
Posted: Wed Oct 25, 2006 2:26 am
by rameshrr3
I changed the specification to
dt_1:string[23] = string_from_timestamp(dt)
and the warning has got suppressed.
how do i specify the 'timestamp_format' in
string_from_timestamp[timestamp_format] (timestamp)
Does anyone have an example?
Thanks
Ramesh
Posted: Wed Oct 25, 2006 3:29 am
by kumar_s
Try passing the format like %yyyy-%mm-%dd %hh:%nn:%ss.
Posted: Wed Oct 25, 2006 3:52 am
by rameshrr3
Actually i guess im losing out on the correct syntax while passing the format string to the function. Not able to get a clue from the document either
I tried
Code: Select all
sophis_changed_dt1:string[23] = string_from_timestamp["%yyyy-%mm-%dd %hh:%nn%:ss"](sophis_changed_dt)
but it throws an error
Code: Select all
main_program: Error parsing modify adapter: Error in binding: Parsing parameters ""%yyyy-%mm-%dd %hh:%nn%:ss"" for conversion "string=string_from_timestamp[%1900yy-%mm-%dd %hh:%nn:%ss](timestamp)": APT_Conversion_String_TimeStamp: Invalid Format ["%yyyy-%mm-%dd %hh:%nn%:ss"] used for string_from_time type conversion
Expected destination field selector, got: ")"; input:
DROP old_ds_sec_id
;
sophis_changed_dt1:string[23] = string_from_timestamp["%yyyy-%mm-%dd %hh:%nn%:ss"](sophis_changed_dt)
;
Thanks
Ramesh
Posted: Wed Oct 25, 2006 4:16 am
by kumar_s
What is the format of the timestamp in input?
Posted: Wed Oct 25, 2006 4:27 am
by rameshrr3
Okay i got it , the timestamp format also has milli seconds, . I changed teh format string , but i still get an error if i specify the format string . Since there are no examples on using parallel functions, im quite clueless
Code: Select all
main_program: Error parsing modify adapter: Error in binding: Parsing parameters "%yyyy-%mm-%dd %hh:%nn%:ss.3" for conversion "string=string_from_timestamp[%1900yy-%mm-%dd %hh:%nn:%ss](timestamp)": APT_Conversion_String_TimeStamp: Invalid Format [%yyyy-%mm-%dd %hh:%nn%:ss.3] used for string_from_time type conversion
Expected destination field selector, got: ")"; input:
DROP old_ds_sec_id
;
sophis_changed_dt1:string[23] = string_from_timestamp[%yyyy-%mm-%dd %hh:%nn%:ss.3] (sophis_changed_dt)
;
The job doesnt abort if i DO NOT specify format string, but i lose the millisecond information.
Posted: Wed Oct 25, 2006 6:02 am
by kumar_s
What is the input format? 1900yy Is your input has only 2 digit of Year patetrn. Does your job abort now? What is your source? If it is database, why can't you convert it during extract?
Posted: Wed Oct 25, 2006 7:11 am
by rameshrr3
The year is 4 digit and picked up from a sybase database via Stored Proc. Since SP is supplied by another team, i cannot change the same. So im trying to use modify stage to change the field data type.
the input data looks like this
"2004-12-20 02:33:01.147"
Output should be same , but as a varchar field, hence im using explicit transformation.
If i do not use the timestamp format string , job gives an output with millisecond information truncated. When i use timestamp format string , i get the error and job aborts , as described in my previous posts. I dont know if im making a syntax mistake in the specification either.
Posted: Wed Oct 25, 2006 8:31 am
by rameshrr3
Like i guessed ,it was almost unnoticed
I changed the format string from
to
and everything worked beautifully.
Thanks
Ramesh