locked
SSMA Oracle to SQL Server date\time issues RRS feed

  • Question

  • I am able to migrate my data fine but my Access front end does not recognize the date\time columns correctly.  I am using the same Access front end that I had used with Oracle and just modifying what I need to in order to make it work with SQL Server but the only hangup seems to be the various date\time columns.  Between the various tables in Oracle, I have 3 columns with data type of date and 2 columns with data type of timestamp.  Using SSMA I have tried making the destination SQL columns as datetime, datetime2, or just date but no matter what it seems like Access treats them like a character string.  This may be an Access issue but I am starting to wonder if it is a backend issue.  I gather that the actual data is stored differently in the table then how it is being displayed, but Access, SQL Server Management Studio, and the data tab in SSMA all make perfectly recognizable (if not exactly consistant) display of the dates and times. 
    Tuesday, May 8, 2012 10:10 PM

Answers

  • Thanks for the response Alberto but I did not have occasion to actually look into your suggested solution since I had found one yesterday but didn't get to test it until today.

    Per this thread I found yesterday http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/7305ad70-0084-43ec-8379-532d430da9d4 I had already downloaded the Microsoft SQL Server 2012 Native Client ODBC driver just before I went home and planned to test it first thing this morning to see if it was an ODBC issue.  After installing the driver I recreated my DSN using the SQL Server Native Client 11.0 ODBC driver and now the Access front end recognizes the date\time data types and all of the formatting settings like short date, medium time, ddd: m/d/yy, and so forth work correctly again

    I guess I had mistakenly assumed Windows 7 would have come with a more recent SQL Server ODBC driver, I know that SQL Server 2012 is newer than Windows 7 but the data type of datetime is from previous versions of SQL Server so I had thought it would work.  Anyway, the new ODBC driver fixed all the Access date\time recognition and display issues.

    • Marked as answer by MnM Show Wednesday, May 9, 2012 5:57 PM
    Wednesday, May 9, 2012 5:57 PM

All replies

  • Hello,

    Create a Microsoft Access project and create a form  based on a SQL Server table that have columns of datetime data type. Maybe you like the behavior of datetime fileds on  Access Projects.

    Use the following resource to create an Access Project.

    http://office.microsoft.com/en-us/access-help/create-an-access-project-HA010167953.aspx

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, May 9, 2012 2:43 AM
  • Thanks for the response Alberto but I did not have occasion to actually look into your suggested solution since I had found one yesterday but didn't get to test it until today.

    Per this thread I found yesterday http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/7305ad70-0084-43ec-8379-532d430da9d4 I had already downloaded the Microsoft SQL Server 2012 Native Client ODBC driver just before I went home and planned to test it first thing this morning to see if it was an ODBC issue.  After installing the driver I recreated my DSN using the SQL Server Native Client 11.0 ODBC driver and now the Access front end recognizes the date\time data types and all of the formatting settings like short date, medium time, ddd: m/d/yy, and so forth work correctly again

    I guess I had mistakenly assumed Windows 7 would have come with a more recent SQL Server ODBC driver, I know that SQL Server 2012 is newer than Windows 7 but the data type of datetime is from previous versions of SQL Server so I had thought it would work.  Anyway, the new ODBC driver fixed all the Access date\time recognition and display issues.

    • Marked as answer by MnM Show Wednesday, May 9, 2012 5:57 PM
    Wednesday, May 9, 2012 5:57 PM