September 30, 2009 at 1:17 pm
I'm using the import wizard SQL05...
I"m importing a txt file, all fields (including date fields) are importing okay until I get to a date field that has some empty values. The date fields that are all populated import fine, it's only when I try to import a date that has some empty values.
Allow Nulls is checked for these date fields..
The error(s) I get is:
Invalid character value for cast specification
There was an error with input column "MI_EXPIRE_DT" (2730) on input "Destination Input"
I don't know why it won't take empty dates..do I need to put in a default value?
Is it possible I'm getting garbage in these date fields (like a space for example)?
September 30, 2009 at 2:33 pm
It's possible. You should open the file yourself and see exactly what is there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2009 at 2:36 pm
Thanks for answering..
I think the issue is I'm trying to insert 'empty' into a datetime column...
Should I have the vender change the empty's to 'NULL'?
September 30, 2009 at 7:49 pm
krypto69 (9/30/2009)
Thanks for answering..I think the issue is I'm trying to insert 'empty' into a datetime column...
Should I have the vender change the empty's to 'NULL'?
No, you should get them to change it to ''.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2009 at 7:52 pm
Though in truth, my actual preference is to import to staging tables where I can take care of this kind of thing myself before putting it into the production tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2009 at 2:37 am
RBarryYoung (9/30/2009)
krypto69 (9/30/2009)
Thanks for answering..I think the issue is I'm trying to insert 'empty' into a datetime column...
Should I have the vender change the empty's to 'NULL'?
No, you should get them to change it to ''.
Or create a derived column that fixes the dodgy data and use that in your import instead.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 2, 2009 at 9:15 am
I am using the staging table and verify in a case statement if it is valid date to write to the production table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply