Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2010-04-22 : 11:27:38
|
| I have OrderSent column(timestamp datatype) in AS400 file with the below values:2009-03-11-13.07.24.9220002009-02-18-13.07.24.924000When I am trying to load this AS400 column using SSIS into a SQL column with datetime datatype, I am getting the below error:Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".In order to use the derived column component,I wrote this expression for the AS400 date using the derived column component to show in the ISO format standard "yyyy-MM-ddThh:nn:ss.fffff":SUBSTRING(DerivedCol,1,4) + "-" + SUBSTRING(DerivedCol,6,2) + "-" + SUBSTRING(DerivedCol,9,2) + "T" + SUBSTRING(DerivedCol,12,2) + ":" + SUBSTRING(DerivedCol,15,2) + ":" + SUBSTRING(DerivedCol,18,9)I am getting this error:Source: "Microsoft SQL Server Native Client 10.0"Hresult: 0x80004005 Description: "Invalid character value for cast specification".any ideas? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 11:41:13
|
Have a look at this:declare @x varchar(255)set @x='2009-02-18-13.07.24.924000'select @xselectSUBSTRING(@x,1,4) + '-' + SUBSTRING(@x,6,2) + '-' + SUBSTRING(@x,9,2) + 'T' + SUBSTRING(@x,12,2) + ':' + SUBSTRING(@x,15,2) + ':' + SUBSTRING(@x,18,2) + '.' +SUBSTRING(@x,21,3)selectconvert(datetime,SUBSTRING(@x,1,4) + '-' + SUBSTRING(@x,6,2) + '-' + SUBSTRING(@x,9,2) + 'T' + SUBSTRING(@x,12,2) + ':' + SUBSTRING(@x,15,2) + ':' + SUBSTRING(@x,18,2) + '.' +SUBSTRING(@x,21,3))
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2010-04-22 : 11:58:43
|
| I am using this expression in derived column component, I tried to convert to dt_dbtimestamp, but getting all errors. Please let me know what I should do to convert to datetime using derived column expression. Thanks. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 12:08:18
|
I only wanted you to change your SUBSTRINGS.Have a closer look.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2010-04-22 : 12:17:04
|
| I also tried the below expression too, but still getting error: Source: "Microsoft SQL Server Native Client 10.0"Hresult: 0x80004005 Description: "Invalid character value for cast specification".SUBSTRING(DerivedCol,1,4) + "-" + SUBSTRING(DerivedCol,6,2) + "-" + SUBSTRING(DerivedCol,9,2) + "T" + SUBSTRING(DerivedCol,12,2) + ":" + SUBSTRING(DerivedCol,15,2) + ":" + SUBSTRING(DerivedCol,18,2) + "." + SUBSTRING(DerivedCol,21,3) |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-04-22 : 13:07:37
|
| Are you assigning this value to a variable? If yes, does it have a proper data type. Did you try running this out of SSIS and in SSMS and see if you are facing the error? |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2010-04-22 : 13:25:51
|
| I am using this expression in derived component column in SSIS. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-04-22 : 13:41:35
|
| Hmmm...Probably your file has some invalid values for this field...Spaces maybe? |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2010-04-22 : 15:01:37
|
| Please help!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 04:07:26
|
| try this too inside expression(DT_DATE) (SUBSTRING(<yourdatevalue>,1, 10 ) + " " + REPLACE(SUBSTRING(<yourdatevalue>, 12,8) ,".",":"))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
SSIS error, help!