timestamp

[laxmi_etl]

Hi,

I have a field 'A' coming in from the source as varchar2(16)
The values is 200702231233 actually it's a datefield.
I need to convert it into timestamp with the fallowing format
2007-02-23 12:33:00
basically when I perform conversion I need to concatenate seconds.
I used string to timestamp functions in different ways but none of them are working.

Moreover the values are getting with ************* marks when I use conversion functions.

Any thaughts on this?

[us1aslam1us]

What is the syntax you are using? Try something like this:

Code: Select all

StringToTimestamp(A[1,4]:'-':A[5,2]:'-':A[7,2]:' ':A[9,2]:':':A[11,2]:"00.000","%yyyy-%mm-%dd %hh:%nn:%ss.3") 

Check for Null values before doing any conversions.

[DSguru2B]

or something like

Code: Select all

StringToTimestamp(in.Col,"%yyyy%mm%dd%hh%nn")

[us1aslam1us]

I guess your target is expecting the timestamp in complete format. you need to concatenate those milliseconds too.

[DSguru2B]

What is your target database? What is the scale and precision set for this field?
When you load with CurrentTimeStamp() and view data, what is the format you see?

[laxmi_etl]

Never mind everything is Ok.
Later we found that there is a problem with the data.

But anyhow thanks for your response.