Page 1 of 1
Invalid character value for cast specification
Posted: Tue Aug 21, 2007 4:01 pm
by 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.
Posted: Tue Aug 21, 2007 4:07 pm
by DSguru2B
Dont you need two characters for month and two for hours?
Posted: Tue Aug 21, 2007 9:11 pm
by 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]")
Re: Invalid character value for cast specification
Posted: Tue Aug 21, 2007 9:50 pm
by changming
dj352001 wrote: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.
Posted: Thu Sep 06, 2007 2:10 pm
by 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?!
Posted: Thu Sep 06, 2007 2:32 pm
by 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?
Posted: Thu Sep 06, 2007 2:39 pm
by 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.
Posted: Thu Sep 06, 2007 3:45 pm
by 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"
Posted: Thu Sep 06, 2007 4:23 pm
by 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.