Post reply

Linked Server MS Access gives date conversion error

  • June 26, 2008 at 5:03 pm

    #193258

    Hi all,

    I established a linked server in SQL2005 that points to an Access database (MDB). When I SELECT from it, it comes up with the following error since one of the date in that row is 12/11/333

    Msg 8114, Level 16, State 8, Line 1

    Error converting data type DBTYPE_DBTIMESTAMP to datetime.

    How should I form the query so that it ignores such dates, or better yet makes it 01/01/1900.

    Please help.

  • July 25, 2008 at 5:30 am

    #848309

    SQL Server 2005 does not support datetime values lower than 17530101 00:00:00.000.

    - Clean up the data at the source; or

    - Export the data from the access database (e.g. as CSV) and import the data into an intermediate table (i.e. with the date/time values stored as characters), then do the clean up and cast to appropriate data type.

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • July 25, 2008 at 5:08 pm

    #848727

    Thanks, but I was able to resolve the issue by using an openquery() where I put the date rules within.i.e. I cased out all the dates.

  • July 26, 2008 at 4:44 am

    #848763

    Frankly, I didn't think OPENQUERY would work. 🙂

    Then again, I haven't even tried it.

    Good on you!

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

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

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