Migrating databases from Oracle to SQL Server can be a daunting task, especially when dealing with date and time data. While tools like the SQL Server Migration Assistant (SSMA) offer automated solutions, relying solely on these tools might not always yield the best performance. In this blog, we share examples of how to optimize conversion of Date and Time Conversion functions, after it was generated by SSMA.

Key differences between Oracle and SQL Server

Oracle and SQL Server handle date and time data differently, which can complicate migrations. In Oracle, the DATE type includes both date and time with second-level precision, whereas the TIMESTAMP type adds fractional seconds. SQL Server, on the other hand, uses several data types such as datetime, datetime2, smalldatetime, date, and time to store similar information.

Moreover, the functions used for date manipulation also differ between the two systems. For example, Oracle’s SYSDATE, SYSTIMESTAMP, TRUNC, and TO_CHAR functions are emulated by the SSMA Extension Pack when SSMA converts code. However, these emulated functions often run slower, impacting performance. This is where native SQL Server functions come into play, offering a more efficient alternative.

Practical guide to Oracle to SQL Server date conversion

Let’s consider an Oracle query that retrieves the current date and truncates the time:

SELECT to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss'), trunc(SYSDATE)
FROM DUAL;
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH24:MI:SS’) TRUNC(SYSDATE)
08/28/2024 05:50:30 2024-08-28 00:00:00

This query, when converted by SSMA for SQL Server, might look like this:

SELECT
ssma_oracle.to_char_date(GETDATE(), 'mm/dd/yyyy hh24:mi:ss'),
ssma_oracle.trunc_date(GETDATE());
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH24:MI:SS’) TRUNC(SYSDATE)
08/28/2024 05:51:28 2024-08-28 00:00:00

While functional, this code is less efficient than using native SQL Server functions:

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy HH:mm:ss'), DATETRUNC(DAY, GETDATE());
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH24:MI:SS’) TRUNC(SYSDATE)
08/28/2024 05:52:05 2024-08-28 00:00:00

The native approach not only simplifies the query but also significantly boosts performance, especially when dealing with large datasets.

Instead of relying on the SSMA Extension Pack for SQL Server, which is slower due to its universal approach, we recommend using native SQL Server functions for specific scenarios. The SSMA Extension Pack is designed to handle a wide range of data conversion scenarios, but this generality often results in lower performance. In many cases, the versatility provided by the Extension Pack isn’t necessary because we already know the specific parameters involved in each function call. By rewriting these function calls to utilize native SQL Server functions, you can achieve significantly better performance tailored to your particular needs.

Which is faster in SQL Server: FORMAT or CONVERT?

When formatting dates in SQL Server, you have options like FORMAT and CONVERT. While FORMAT offers more flexibility, it is also slower. For better performance, especially in large-scale operations, CONVERT is the recommended choice.

For example:

Using FORMAT:

SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy'); -- Slower
08/28/2024

Using CONVERT:

SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101); -- Faster
08/28/2024

Opting for CONVERT not only speeds up query execution but also reduces the overall load on the system, which is crucial when handling extensive data.

Optimizing common date functions

Let’s explore a few more examples where using native SQL Server functions can significantly improve performance during migration:

Date Difference: DATEDIFF

SSMA converted code:

SELECT ssma_oracle.datediff(@END_DATE, @BEGIN_DATE);
0.2467245370370370370

Optimized code:

SELECT DATEDIFF(ss, @BEGIN_DATE, @END_DATE);
0.246725

Last Day of the Month: EOMONTH

SSMA converted code:

SELECT ssma_oracle.last_day(@DEMO_DATE);
2024-08-31 00:00:00

Optimized code:

SELECT EOMONTH(@DEMO_DATE);
2024-08-31

Adding a Time Interval: DATEADD

SSMA converted code:

SELECT ssma_oracle.dateadd(-0.0006, @DEMO_DATE);

Optimized code:

SELECT DATEADD(ms, -0.0006 * 24 * 60 * 60 * 1000, @DEMO_DATE);

By using native SQL Server functions, you avoid unnecessary complexity and improve the execution time of your queries.

Conclusion

Migrating from Oracle to SQL Server is not just about converting syntax; it’s about optimizing performance. While tools like SSMA provide a good starting point, they may not always deliver the best results. By leveraging native SQL Server functions, you can ensure your database operates at its highest efficiency.

If you want to achieve maximum performance during data migration and optimize your database operations, the team of experts at ABCloudz is ready to assist you. We will perform the migration with minimal risk and ensure the best results.

Contact us today to discuss your database migration or modernization project.

Questions? Contact us