Post reply

BCP Import Error with date '99990101'

  • May 12, 2010 at 8:40 am

    #221471

    I am using BCP to perform a bulk insert into a table. Below is an example line item from the data being inserted. The issue is the the highlighted date. For some reason I am getting this error:

    Microsoft OLE DB Provider for SQL Server:Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 24 (Next_Renewal_Date)

    Here is the referenced row:

    1514830|Flintsone|Fred| |F|A|19930802||106090|004OT|FT|0.900|fred@flintsone.org|5124875356|123 Reynosa Drive|Austin|TX|78799|Specialty Transport Nurse RN|1214|00.15|20091018|LMSW|99990101|RN LICENSE||RN|20120531|505119|TX||||

    The file being imported is a text file and the column "Next_Renewal_Date" data type is smalldatetime.

    If I change the date from 99990101 to 20500101 it works fine so I assume that he year 9999 is not a valid year in SQL???

  • May 12, 2010 at 8:43 am

    #1166268

    There are limits to the max and min dates allowed for Samlldatetime date types. Look in BOL.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 12, 2010 at 10:54 am

    #1166382

    Mike01,

    Thank for the reply. I am not sure what BOL is but your reply gave me something to do a google search on and I found the answer.

    The max date for smalldatetime is June 6, 2079. I switched the data type to DateTime and now works well.

    Thanks

  • May 12, 2010 at 12:03 pm

    #1166441

    I'm glad to hear you found the answer. Just an FYI, BOL stands for Books On Line. if is a set of documentation that Microsoft creates to help with SQL Server.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply