https://www.sqlines.com/ 2026-04-24T11:35:10+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2013-02-10T14:23:03+00:00 https://www.sqlines.com/oracle-to-sql-server/add_months?rev=1360506183&do=diff In Oracle, ADD_MONTHS function adds the specified number of months to a datetime value. In SQL Server, you can use DATEADD function. Oracle: -- Add 3 months to the current date (February 10, 2013): SELECT ADD_MONTHS(SYSDATE, 3) FROM dual; # 10-MAY-2013 text/html 2022-10-11T23:01:33+00:00 https://www.sqlines.com/oracle-to-sql-server/char?rev=1665529293&do=diff Oracle and SQL Server provide the CHAR(n) data type to store fixed-length character data right-padded with blanks to the specified n length: Oracle SQL Server CHAR CHAR CHAR(n) CHAR(n) CHAR(n CHAR) Size in characters CHAR(n BYTE) Size in bytes 1 <= n <= 2000, default is 1 1 <= n <= 8000, default is 1 Size n in characters or bytes, but the storage limit is 2000 bytes Size n in bytes Default length semantics is defined by NLS_LENGTH_SEMANTICS parameter Trailing spaces are retain… text/html 2022-08-12T20:48:35+00:00 https://www.sqlines.com/oracle-to-sql-server/connect_by_prior?rev=1660337315&do=diff In Oracle, you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. Microsoft SQL Server (MSSQL) allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality. Rows Generator One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows: text/html 2018-01-06T08:43:00+00:00 https://www.sqlines.com/oracle-to-sql-server/cursors?rev=1515228180&do=diff In Oracle and SQL Server you can declare a cursor, use OPEN, FETCH and CLOSE statements, but besides syntax there are some semantic differences. Cursor Declaration and Scope In Oracle cursors are always local i.e visible only within the scope where they are declared. In SQL Server a cursor can be declared as LOCAL or GLOBAL. text/html 2017-09-21T10:44:34+00:00 https://www.sqlines.com/oracle-to-sql-server/date?rev=1505990674&do=diff 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) data types when migrating Oracle DATE. text/html 2024-09-13T20:51:00+00:00 https://www.sqlines.com/oracle-to-sql-server/datetime_arithmetic?rev=1726260660&do=diff Datetime arithmetic involves addition and subtraction operations on date and time values using + and - operators. Add and Subtract Days In Oracle and SQL Server, when you add an integer value to a datetime, you add days: Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; -- Output the current date and time SELECT SYSDATE FROM dual; # 2012-06-11 00:39:06 -- Add 3 days to the current day SELECT SYSDATE + 3 FROM dual; # 2012-06-14 00:39:06 text/html 2013-02-09T22:43:28+00:00 https://www.sqlines.com/oracle-to-sql-server/decode?rev=1360449808&do=diff In Oracle, you can use DECODE function to evaluate a list of expressions, and if a value is matched return the corresponding result. In SQL Server, you can use CASE expression that is also supported by Oracle. Note that NULL values in DECODE function and CASE expression are handled differently . text/html 2017-10-05T15:01:59+00:00 https://www.sqlines.com/oracle-to-sql-server/execute_immediate?rev=1507215719&do=diff In Oracle PL/SQL, you can use EXECUTE IMMEDIATE statement to execute a dynamic SQL statement. In Microsoft SQL Server Transact-SQL, you can use EXECUTE statement or EXECUTE sp_executesql stored procedure to execute dynamic SQL statements. Oracle PL/SQL: text/html 2023-07-30T19:15:32+00:00 https://www.sqlines.com/oracle-to-sql-server/from_tz?rev=1690744532&do=diff In Oracle the FROM_TZ function allows you set the specified time zone for a timestamp value that does not have the timezone. In SQL Server you can use AT TIME ZONE operator, but note that the supported timezone values can be different (see below). text/html 2022-12-02T09:04:15+00:00 https://www.sqlines.com/oracle-to-sql-server/instr?rev=1669971855&do=diff In Oracle the INSTR function allows you to find the position of substring in a string. It accepts 2, 3 or 4 parameters. SQL Server provides the CHARINDEX function to find the position of substring, but it accepts only 2 or 3 parameters, so you have to use an user-defined function when converting Oracle INSTR with 4 parameters. text/html 2023-05-31T13:41:46+00:00 https://www.sqlines.com/oracle-to-sql-server/listagg?rev=1685540506&do=diff In Oracle you can use LISTAGG function to concatenate strings from multiple rows into a single row value. In SQL Server you can use STRING_AGG function but note that it is available since SQL Server 2017 only. Consider a sample table: CREATE TABLE cities (name VARCHAR(30)); -- Insert sample rows INSERT INTO cities VALUES ('Seville'); INSERT INTO cities VALUES ('Warsaw'); INSERT INTO cities VALUES ('Boston'); text/html 2023-05-23T21:57:53+00:00 https://www.sqlines.com/oracle-to-sql-server/months_between?rev=1684879073&do=diff In Oracle, MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. Note that SQL Server DATEDIFF(month, date2, date1) function does not return exactly the same result, and you have to use an user-defined function if you need to fully emulate the Oracle MONTHS_BETWEEN function (see UDF's code below). text/html 2021-09-01T13:08:22+00:00 https://www.sqlines.com/oracle-to-sql-server/plsql_tables?rev=1630501702&do=diff In Oracle you can use PL/SQL Tables to store and process collections of data in a PL/SQL program (procedure, function i.e.). In SQL Server you can use temporary tables to implement such functionality. Oracle PL/SQL Table of Records You can declare, fill in and iterate a PL/SQL table of records as follows: text/html 2013-02-17T13:58:54+00:00 https://www.sqlines.com/oracle-to-sql-server/string_concat?rev=1361109534&do=diff || operator concatenates one or more strings into a single string in Oracle. Quick Example: -- Concatenate strings 'New ' and 'York' SELECT 'New ' || 'York' FROM dual; -- Result: New York || Operator Overview Summary information: text/html 2025-02-08T16:41:51+00:00 https://www.sqlines.com/oracle-to-sql-server/substr?rev=1739032911&do=diff In Oracle, the SUBSTR function returns a substring from a string, starting at the specified position (which can be negative) and for the specified length, or until the end of the string if no length is provided. In SQL Server, you can use the SUBSTRING function, but it does not allow a negative start position with the same meaning as in Oracle, and the substring length must be specified. text/html 2024-10-13T09:50:21+00:00 https://www.sqlines.com/oracle-to-sql-server/sysdate?rev=1728813021&do=diff Oracle SYSDATE function returns the current date and time with seconds precision. In SQL Server you can use GETDATE() function but it includes time with milliseconds precision . Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; -- Get the current date and time SELECT SYSDATE FROM dual; # 2024-10-13 12:46:28 text/html 2025-02-27T08:44:49+00:00 https://www.sqlines.com/oracle-to-sql-server/to_char_datetime?rev=1740645889&do=diff In Oracle, TO_CHAR function converts a datetime value (DATE, TIMESTAMP data types i.e.) to a string using the specified format. In SQL Server, you can use CONVERT or CAST functions to convert a datetime value (DATETIME, DATETIME2 data types i.e.) to a string. text/html 2024-10-13T07:39:22+00:00 https://www.sqlines.com/oracle-to-sql-server/to_date?rev=1728805162&do=diff In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In SQL Server, you can use CONVERT or TRY_CONVERT function with an appropriate datetime style. Oracle: -- Specify a datetime string and its exact format SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual; text/html 2023-01-13T14:48:24+00:00 https://www.sqlines.com/oracle-to-sql-server/to_timestamp?rev=1673621304&do=diff In Oracle TO_TIMESTAMP function converts a string value to TIMESTAMP data type value using the specified format. In SQL Server you can use CONVERT or TRY_CONVERT function with an appropriate datetime style. Note that TRY_CONVERT function is available since SQL Server 2012. text/html 2025-12-15T11:36:50+00:00 https://www.sqlines.com/oracle-to-sql-server/trunc_datetime?rev=1765798610&do=diff In Oracle, the TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (for example, setting the time to zero or setting the date to the first day of the month). By default, it truncates to the day. In SQL Server, you can use various expressions using CONVERT function to get the same result. text/html 2024-09-28T19:53:04+00:00 https://www.sqlines.com/oracle-to-sql-server/update_alias?rev=1727553184&do=diff Both Oracle and SQL Server support SQL UPDATE statement that allows you to modify the existing table rows, but there are some differences so the conversion may be required. UPDATE with Alias Oracle allows you optionally defining an alias for the updated table: text/html 2023-06-24T11:10:09+00:00 https://www.sqlines.com/oracle-to-sql-server/xmlelement?rev=1687605009&do=diff In Oracle XMLELEMENT function allows you to create a XML element (an value with opening and closing tags). In SQL Server you can the special form of SELECT ... FOR XML PATH expression. Consider a sample table: CREATE TABLE cities (name VARCHAR(30), country VARCHAR(30), flag CHAR(1)); -- Insert sample rows INSERT INTO cities VALUES ('Seville', 'Spain', 'S'); INSERT INTO cities VALUES ('Warsaw', 'Poland', 'P'); INSERT INTO cities VALUES ('Boston', 'United States', 'U'); text/html 2022-09-04T07:51:21+00:00 https://www.sqlines.com/oracle-to-sql-server/xmlforest?rev=1662277881&do=diff In Oracle XMLFOREST function allows you to create multiple XML elements (values with opening and closing tags). In SQL Server you can the special form of SELECT ... FOR XML PATH expression. Oracle: SELECT XMLFOREST(country, city) FROM ( SELECT 'Spain' AS country, 'Malaga' AS city FROM dual UNION ALL SELECT 'Turkey' AS country, 'Alanya' AS city FROM dual UNION ALL SELECT 'Greece' AS country, 'Marmaras' AS city FROM dual ) t;