Timestamp conversion

[Sgiri1]

hi please let me know how to do following time stamp conversion.

My i/p is 2009-08-05 23:35:06

and the required o/p is mm/dd/yyyy hh:mi:ss AM

[Sainath.Srinivasan]

What have you tried?

You already have the format. Use aa for AM/PM.

[chulett]

Is your input already a timestamp or is it coming in as a string?

[Sgiri1]

The i/p is a string and the o/p is a timestamp

[chulett]

Then use StringToTimestamp() with a mask the matches your input format. What you show as 'required output' isn't really true if you are targeting a Timestamp but might be if you were a string.

Or perhaps "timestamp_from_string", depends on the stage you are doing this in. I believe that's the Modify stage conversion function, the former would be used in a Transformer.

[dxk9]

You can use Stringtotimestamp() function in the transformer stage for the conversion.

Ideally it will be something like:

StringToTimestamp(<input data>,"%yyyy/%mm/%dd %hh:%nn:%ss")

Regards,
Divya

[chulett]

Dashes, not slashes.

[dxk9]

The required o/p is "mm/dd/yyyy hh:mi:ss AM " format. So slashes only will solve the issue.

If there is still problem with the output data or if you get any error w.r.t conversion while running, let me know. There are few changes to be done w.r.t the job properties for Timestamp conversions. But that is necessary only if you come across any error.

Regards,
Divya

[priyadarshikunal]

The required o/p is "mm/dd/yyyy hh:mi:ss AM " format. So slashes only will solve the issue.

Have you tried it?

StringToTimestamp requires the Mask for input string not the output. So you need to match that mask with your input string which consists of dashes. Look for page number 191 in parallel job advanced developer guide.

Craig, you are correct.

format in which it will be converted is (by default)
%yyyy-%mm-%dd %hh:%mm:%ss (Page 192)

OP,
Now why you want to change the format of a timestamp. If writing to a flat file then convert it to string once again to get the required format.

If writing to a database, it will be stored in internal format anyways. And all I can guess is "you have a display problem and not a conversion problem".

In case of Display problem check/change the format defined in NLS settings in database or use to_char() or equivalent function to change your display.