warning with string_from_timestamp function

[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

[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

[kumar_s]

Try passing the format like %yyyy-%mm-%dd %hh:%nn:%ss.

[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

[kumar_s]

What is the format of the timestamp in input?

[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.

[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?

[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.

[rameshrr3]

Like i guessed ,it was almost unnoticed
I changed the format string from

Code: Select all

"%yyyy-%mm-%dd %hh:%nn%:ss.3"

to

Code: Select all

"%yyyy-%mm-%dd %hh:%nn:%ss.3"

and everything worked beautifully.

Thanks
Ramesh