Invalid character value for cast specification

[dj352001]

I'm getting an Invalid character value for cast specification
on this column:
It's coming from a sequential file

RATING_PUBLSHD_DT = "9/13/1999 0:00:00"

I'm trying to insert it into sql server, as a timestamp. (23,3). Not sure why i'm getting an error on this for.

[DSguru2B]

Dont you need two characters for month and two for hours?

[ray.wurlod]

If that's what's in the text file, that's what's in the text file. You need to convert it to a format acceptable to SQL Server.

Code: Select all

Oconv(Iconv(Field(InLink.TheString," ",1,1),"DMDY"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(Field(InLink.TheString," ",2,1),"MTS"),"MTS:[2,2,2]")

[changming]

I'm getting an Invalid character value for cast specification
on this column:
It's coming from a sequential file

RATING_PUBLSHD_DT = "9/13/1999 0:00:00"

I'm trying to insert it into sql server, as a timestamp. (23,3). Not sure why i'm getting an error on this for.

I believe you format the date as yyyymmddhhmmss as varchar2, that will work.

[dj352001]

I'm still having trouble with this one....
I try to use oconv and all i get is an empty set ""

this is the data coming in
RATING_PUBLSHD_DT = "2002-16-3 0:00:00"

YYYY-DD-MM

it's inserting into a sql table defined as timestamp 23,3 but i'm getting an error that says
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Invalid character value for cast specification

any ideas?!

[chulett]

What is the oconv syntax you are using?
What format are you trying to convert it to?
What data type is the target field in your job?
What does your insert SQL look like?

[dj352001]

oconv is: oconv(LP_TAI_LOAD.RATING_PUBLSHD_DT, "D-MDY[2,2,4]")
I'm just trying to insert it into the sql table, but i keep on getting invalid character value for cast specification, even if i just leave the column with no oconv statement.
Target is: timestamp (23,3)
sql is: simple insert statement.

If i remove this column the job runs fine, just don't understand why it would be causing this if in the sequential file it's defined as a date. thanks for the response.

[ray.wurlod]

You first need an Iconv() function - two actually, to get the incoming data into DataStage internal format. Only then can you successfully use Oconv() to change to the desired external format.

Code: Select all

Oconv(Iconv(Field(InLink.TheString," ",1,1),"DYMD"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(Field(InLink.TheString," ",2,1),"MT"), "MTS:") : ".000"

[dj352001]

I tried to use the iconv function put all i got was "".

What i did was opened the txt file and replaced 0:00:00 with 00:00:000.

Not sure why datastage was returning "" when i used the iconv or the oconv function, thanks for the help everyone.