Hi,
I'm loading date columns from a sequential file-> transformer->table.
The input date is in the fomat DD/MM/YY
and the destination column is of datatype Timestamp.
I get the error: Invalid character value for cast specification.
I guess the error is because I'm loading a Date( without timestamp) into a Timestamp datatype column.
But my destination is SQL Server and I couldn't find a datatype 'Date'.
Also, I cannot use varchar to store the date as I need to perform date validations as well.
Can anyone suggest a way to overcome this error? That is, how can I load a value like 7/1/07 into a Timestamp column?
Thank you in advance.
Date loading problems
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
Hi ,
Since the input is in DD/MM/YY, i tried to convert it to the standard format for SQL Server before concatenating.
So, used formula
OCONV(ICONV("DSLink9.Field003","D2-DMY[2,2,2]"),"D2-MDY[2,2,4]"):" ":"00:00:00.000"
But I still get error:
Invalid character value for cast specification
DATE = " 00:00:00.000"
The target datatype is Timestamp 23,3
Can anyone explain why DS still doesnt consider the Date part?
On my part, I changed the target definition to varchar and put in ICONV("DSLink9.Field003","D2-DMY[2,2,2]") to check if atleast the Iconv is working ok.
But even though the job runs fine, the date column remians blank
Its the first time I'm handling Iconv\Oconv operations, so I would really appreciate some guidance on this
Thanks in advance....
Since the input is in DD/MM/YY, i tried to convert it to the standard format for SQL Server before concatenating.
So, used formula
OCONV(ICONV("DSLink9.Field003","D2-DMY[2,2,2]"),"D2-MDY[2,2,4]"):" ":"00:00:00.000"
But I still get error:
Invalid character value for cast specification
DATE = " 00:00:00.000"
The target datatype is Timestamp 23,3
Can anyone explain why DS still doesnt consider the Date part?
On my part, I changed the target definition to varchar and put in ICONV("DSLink9.Field003","D2-DMY[2,2,2]") to check if atleast the Iconv is working ok.
But even though the job runs fine, the date column remians blank
Its the first time I'm handling Iconv\Oconv operations, so I would really appreciate some guidance on this
Thanks in advance....
Rea Pullan Antony
Ditch the quotes around the Link.Field, they turn it from your incoming value to a literal string. And you only need one concatenation:
You sure your output date format shouldn't be YYYY-MM-DD? If this doesn't work for you, post your SQL in the target stage as well.
Code: Select all
OCONV(ICONV(DSLink9.Field003,"D2-DMY[2,2,2]"),"D2-MDY[2,2,4]"):" 00:00:00.000"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Get into the habit of renaming stages and links with meaningful names as soon as you place them in the design area, and renaming sequential file columns to meaningful names while on the Define tab of the Import wizard.
DSLink9.Field003 is anathema.
DSLink9.Field003 is anathema.
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.