trying to truncate the string

[gayatri]

Hi,

I am trying to truncate part of the string. This is the raw data.

1/1/2007 0:00:00

I only need date from this. How can i extract only the date.
I used Left function, not working. Is there any truncate function in datastage that I can use.

Thanks

[DSguru2B]

Welcome Aboard
Use the Field Function. Use DataStage help to see how it works.
For your problem, use this

Code: Select all

Field(in.Col, " ",1)

[gayatri]

Welcome Aboard :P
Use the Field Function. Use DataStage help to see how it works.
For your problem, use this

Code: Select all

Field(in.Col, " ",1)

I used this function. I am getting this error.

Field(in.Col, "0:00:00 ",1)

it returned

"1/1/2"

Please correct me if I am doing this wrong.

[johnthomas]

Try extracting using string function the date portion from timestamp , since leanth of the date portion varies depending whether its a 1 digit day or month

[johnthomas]

try Field(in.Col, " ",1) in place of Field(in.Col, "0:00:00 ",1) as suggested by dsguru

[narasimha]

As mentioned above use

Code: Select all

Field(in.Col, " ",1)

You are using

Code: Select all

Field(in.Col, "0:00:00 ",1)

[DSguru2B]

In the expression that i provided earlier, "in.Col" is your input column. You need to replace that with your inputlink.columnname.

[gayatri]

In the expression that i provided earlier, "in.Col" is your input column. You need to replace that with your inputlink.columnname. ...

Thanks so much function worked
but I am getting another error now

SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Oracle][ODBC]Datetime field overflow.

Any suggestions.

[DSguru2B]

Check this post

[kumar_s]

If you are using database stage, you can use TO_DATE() to convert it into the required date format.