breaking on null dates?

  • 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)?

  • 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]

  • 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'?

  • 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]

  • 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]

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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