SQL Script Conversion Tips

Owned by Daniel Popham
Last updated: Sept 24, 2024 by Yaxing Liu
1 min read

  1. Comparing SQL Server and Oracle datatypes

This tip will compare the different datatypes offered by both database systems so those of us that work with both technologies or are planning a migration from one technology to the other have a guide that they can use as a quick reference.

image-20240219-140752.png
image-20240219-140854.png

 

  1. Oracle to Microsoft SQL Server (MSSQL) Migration

Above is the tip from Microsoft to use SQL Server Migration Assistant (SSMA) for Oracle.

  1. Convert Oracle database objects to Microsoft SQL

This article teaches how to convert Oracle database objects to the Microsoft SQL platform by using Database Schema Conversion Toolkit (Oracle to Microsoft SQL) extension. This might be a new tool according to the publish date of this article was Oct 11, 2023.

  1. Common functions to convert

    1. Get time part of date columns with datetime as data type:

      1. to_char(PAT2.BIRTH_TM, 'HH24:MI') AS BIRTH_TIME → CONVERT(VARCHAR, BIRTH_TM, 108)

    2. Get date part of date columns with datetime as data type: SELECT CAST(getdate() AS date);

    3. Calculate the date difference between two dates Example:

      1. trunc ( months_between(pe.CHECKIN_TIME, p.BIRTH_DATE)/12,0) → round( DATEDIFF( month, p.BIRTH_DATE, pe.CHECKIN_TIME)/12, 0 )

    4.