locked
SSMA Datetype mapping for DATE datatype of Oracle to SQL Server RRS feed

  • Question

  • In the SSMA project what I am observing is for mapping a column in Oracle with datatype DATE, the corresponding SQL Server datatype is given as DATETIME, DATETIME2, DATETIME2(Precision), SMALLDATETIME. My target SQL Server version is 2012 and SQL Server 2012 supports DATE datatype without having time.

    My question is why SSMA not allowing us to SELECT the DATE datatype instead of DATETIME? We don't need time part in those columns where Oracle has the column datatype defined as DATE. When I change the datatype from datetime to DATE from Edit type mapping option, it is not showing nor allowing to type DATE datatype.

    Please let me know if there is an option to include DATE datatype part of the datatype mapping?.

    Regards,

    Phaneendra


    Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.


    Monday, October 28, 2019 9:07 AM

Answers

  • Hi Phaneendra,

    >> why SSMA not allowing us to SELECT the DATE datatype instead of DATETIME?

    In Oracle, DATE data type stores date and time data (year, month, day, hour, minute and second).

    SQL Server DATE data type (available since SQL Server 2008) can store only year, month and day, so to preserve the time part you have to use DATETIME or DATETIME2(0)(by dafault) data types when migrating Oracle DATE.

    For more details, please refer to DATE Data Type - Oracle to SQL Server Migration and Type Mapping.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 29, 2019 5:59 AM

All replies

  • You can editi the data type mapping: Edit Type Mapping (OracleToSQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 28, 2019 9:35 AM
  • Thank you for quick reply. My problem is on the target type, I don't see the DATE datatype. So, even if I add new mapping/try editing existing mapping, I will not be able to change the target type to DATE.

    Regards,

    Phaneendra


    Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

    Monday, October 28, 2019 11:18 AM
  • Hi Phaneendra,

    >> why SSMA not allowing us to SELECT the DATE datatype instead of DATETIME?

    In Oracle, DATE data type stores date and time data (year, month, day, hour, minute and second).

    SQL Server DATE data type (available since SQL Server 2008) can store only year, month and day, so to preserve the time part you have to use DATETIME or DATETIME2(0)(by dafault) data types when migrating Oracle DATE.

    For more details, please refer to DATE Data Type - Oracle to SQL Server Migration and Type Mapping.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 29, 2019 5:59 AM
  • Hi Phaneendra,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 4, 2019 6:30 AM
  • I understand the point you mentioned in your response. We have decided to go with the option of choosing DATETIME since that is good enough in our context. It is same as what you have suggested.

    Regards,

    Phaneendra


    Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

    Monday, November 4, 2019 10:20 AM