Post reply

reading text datetime value with OPENROWSET and MICROSOFT.ACE.OLEDB12.0 changes datetime value

  • May 6, 2010 at 7:48 pm

    #221297

    I have a text file "c:\temp\captell\test.csv" with the following contents

    DATETIME

    2010-05-04 07:00

    note the datetime column value "2010-05-04 07:00"

    I'm reading this data using OPENROWSET as follows

    select *

    FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=c:\temp\captell;HDR=Yes', 'SELECT * FROM [test.CSV]')

    the output from this is

    DATETIME

    2010-05-04 06:59:59.997

    For some unknown reason the datetime "2010-05-04 07:00" was read in as "2010-05-04 06:59:59.997"

    If I create a schema.ini file in the c:\temp\captell directory for this file with the following contents

    [test.CSV]

    DateTimeFormat="yyyy-mm-dd"

    This forces the datatime to be read as varchar

    DATETIME

    2010-05-04 07:00

    I can then correctly cast the datetime column to datetime. It appears that the promlem lies with the MICROSOFT.ACE.OLEDB.12.0 driver not reading the datatime correctly.

    Has anyone come across this problem before? and if so, any suggestions on how to fix it?

  • May 6, 2010 at 8:10 pm

    #1163746

    A little more information for the pot.

    If I add some extra rows to the csv file

    DATETIME

    2010-05-04 07:00

    2010-05-04 07:00:00

    2010-05-04 08:00:00

    2010-05-04 07:10:10

    These are read in as

    2010-05-04 06:59:59.997

    2010-05-04 06:59:59.997

    2010-05-04 08:00:00.000

    2010-05-04 07:10:10.000

    Note the 2010-05-04 08:00:00 data is correctly read at 2010-05-04 08:00:00.000 whereas the 2010-05-04 07:00:00 is incorrectly read as 2010-05-04 06:59:59.997

  • May 7, 2010 at 9:32 am

    #1164200

    How many hours are you from zulu (UTC)? =Marty=

    Regards,

    R Martin Ladner
    futureec.com

  • May 7, 2010 at 2:00 pm

    #1164436

    Melbourne Australia, UTC + 10:00

  • May 7, 2010 at 2:33 pm

    #1164446

    Adrian Heald (5/6/2010)Note the 2010-05-04 08:00:00 data is correctly read at 2010-05-04 08:00:00.000 whereas the 2010-05-04 07:00:00 is incorrectly read as 2010-05-04 06:59:59.997

    Differential from 07:00:00.000 to 06:59:59.997 is pretty close to the 3.3ms accuracy of the datetime datatype - I know it doesn't explain why 08:00:00 reads correctly.

    Some background information can be found here... http://www.karaszi.com/SQLServer/info_datetime.asp

    Have you tried to cast() it?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • May 7, 2010 at 2:45 pm

    #1164452

    If I specify a SCHEMA.INI file with DateTimeFormat="yyyy-mm-dd" the datetime column is read in as varchar correctly, I can then cast it to datetime. It therefore appears that the issue lies with the OLEDB driver not processing the datatime column correctly.

    Incidentally, I downloaded and installed Office 2010 yesterday (64 bit edition), this has new 64 bit OLEDB drivers that allow openrowset access to text, excel files etc. from a 64 bit edition of SQL Server. This version demonstrated the same issue.

  • May 7, 2010 at 9:58 pm

    #1164518

    A little more information on this one.

    If I use the MICROSOFT.JET.OLEDB.4.0 driver I get the same problems.

    If I use ODBC, Driver={Microsoft Excel Driver (*.xls) it works fine.

  • April 17, 2011 at 12:45 pm

    #1312531

    Adrian Heald (5/7/2010)

    A little more information on this one.

    If I use the MICROSOFT.JET.OLEDB.4.0 driver I get the same problems.

    If I use ODBC, Driver={Microsoft Excel Driver (*.xls) it works fine.

    Hi Adrian,

    I know this is an older post but, if you're still out there, I have a favor to ask. Can you post your entire OPENROWSET command for the above? It would likely save me a huge amount of time.

    Thanks, Adrian.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 17, 2011 at 5:02 pm

    #1312565

    Hi Jeff,

    The following should help.

    select *

    FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=c:\temp;HDR=Yes', 'SELECT * FROM [test.CSV]')

    select *

    FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=c:\temp;','select * from [test.CSV]')

    Further to this, one of my clients put this problem to Microsoft through their premium support channel and the response from Microsoft was that this was by design and not a bug!!!

    As suspected it's is to do with the change of precision, when I pointed out that ODBC didn't have the precision issue their response was that ODBC used a different design. They didn't seem to get that a design that doesn't work is just as bad as a bug, if not more so.

    Hope this helps.

  • April 17, 2011 at 5:07 pm

    #1312566

    Indeed it does help. Thanks, Adrian. I'll give them a whirl.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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