Invalid character value for cast specification
Moderators: chulett, rschirm, roy
Invalid character value for cast specification
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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]")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Invalid character value for cast specification
I believe you format the date as yyyymmddhhmmss as varchar2, that will work.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'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?!
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?!
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.