https://sqlines.com/ 2026-04-29T15:37:58+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2025-01-12T13:45:59+00:00 https://sqlines.com/sql-server-to-oracle/cast?rev=1736689559&do=diff In SQL Server you can use CAST function to convert an expression to another data type. In Oracle you can also use CAST function but data types can be different. SQL Server: -- Convert number to string SELECT CAST(123.45 AS VARCHAR(6)); Oracle: text/html 2023-01-19T16:24:55+00:00 https://sqlines.com/sql-server-to-oracle/convert_datetime?rev=1674145495&do=diff In SQL Server you can use CONVERT function to convert a string expression in the specified format (style) to a datetime data type (DATE, DATETIME etc.). In Oracle you can to use TO_TIMESTAMP function. Note that SQL Server CONVERT and Oracle formats are different. text/html 2025-01-11T20:02:56+00:00 https://sqlines.com/sql-server-to-oracle/convert_number?rev=1736625776&do=diff In SQL Server, you can use the CONVERT function to convert an expression to a numeric type. In Oracle, you can use the CAST function but data types can be different. SQL Server: -- Convert string to number SELECT CONVERT(DECIMAL(5,2), '123.45'); /* 123.45 */ text/html 2024-12-04T21:17:01+00:00 https://sqlines.com/sql-server-to-oracle/convert_string_datetime?rev=1733347021&do=diff In SQL Server, you can use the CONVERT function to convert a DATETIME value to a string with the specified format. In Oracle you can use the TO_CHAR function. Note that SQL Server CONVERT and Oracle TO_CHAR formats are different. SQL Server: -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds) SELECT CONVERT(VARCHAR, GETDATE(), 121); # 2022-12-27 14:33:49.413 text/html 2013-10-15T08:40:48+00:00 https://sqlines.com/sql-server-to-oracle/create_type?rev=1381826448&do=diff In SQL Server you can use CREATE TYPE statement to create a user-defined type (UDT) as an alias for a system data type. You can optionally specify DEFAULT, NOT NULL and CHECK constraint. The UDT can be used in a primary or unique constraint in SQL Server. text/html 2023-02-19T14:15:26+00:00 https://sqlines.com/sql-server-to-oracle/dateadd?rev=1676816126&do=diff In SQL Server you can use DATEADD function to add the specified number of units to a datetime value. In Oracle you have to use an INTERVAL expression. SQL Server: -- Add 1 day to the current datetime SELECT DATEADD(DAY, 1, GETDATE()); # 2023-02-11 17:19:59.360 text/html 2022-12-29T15:22:27+00:00 https://sqlines.com/sql-server-to-oracle/datename?rev=1672327347&do=diff In Microsoft SQL Server you can use DATENAME function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value. In Oracle you can use TO_CHAR function with the specific format type. SQL Server: -- Get the name of week day SELECT DATENAME(dw, '2022-12-29') # Thursday text/html 2022-12-27T23:10:00+00:00 https://sqlines.com/sql-server-to-oracle/day?rev=1672182600&do=diff In SQL Server you can use DAY function to get the day (an integer number) of the specified datetime. In Oracle you have to use EXTRACT(DAY FROM datetime) function. SQL Server: -- Get the day SELECT DAY('2022-12-28'); # 28 Oracle: -- Get the day SELECT EXTRACT(DAY FROM DATE '2022-12-28') FROM dual; # 28 text/html 2024-07-28T16:44:22+00:00 https://sqlines.com/sql-server-to-oracle/getdate?rev=1722185062&do=diff In SQL Server the GETDATE function returns the current date and time with milliseconds precision without the time zone for the current database instance. In Oracle you can use the SYSTIMESTAMP function but it includes time zone. SQL Server: -- Get the current date and time with milliseconds precision SELECT GETDATE(); # 2024-07-28 13:13:11.640 text/html 2024-10-23T08:14:04+00:00 https://sqlines.com/sql-server-to-oracle/patindex?rev=1729671244&do=diff In SQL Server, the PATINDEX function returns the position of the first occurrence of the pattern in the string. Depending on the database collation, the pattern search can be case-insensitive. In Oracle, you can use the REGEXP_INSTR function but note that the order of parameters and the pattern syntax are different. text/html 2023-12-19T15:55:31+00:00 https://sqlines.com/sql-server-to-oracle/return_table?rev=1703001331&do=diff In SQL Server a user-defined function can return a table (multiple rows). This allows you to execute a function in the FROM clause of a SELECT statement. In Oracle you can use a pipelined function. SQL Server: -- Iterator procedure returning 3 rows to caller CREATE FUNCTION cities_list() RETURNS TABLE AS RETURN SELECT 'Boston' AS city UNION ALL SELECT 'San Francisco' AS city UNION ALL SELECT 'Chicago' AS city GO text/html 2025-01-12T08:01:42+00:00 https://sqlines.com/sql-server-to-oracle/round?rev=1736668902&do=diff In SQL Server, the ROUND function rounds the number to the nearest value with the specified precision, or truncates the number to the specified precision. In Oracle you can use the ROUND and TRUNC functions. SQL Server: -- Round to 1 decimal digit (rounded to lower value as 4 <= 5) SELECT ROUND(123.34, 1); /* 123.30 */ -- Round to 1 decimal digit (rounded to upper value as 6 > 5) SELECT ROUND(123.36, 1); /* 123.40 */ -- When the 3rd parameter is non-zero then the val… text/html 2025-01-11T14:43:07+00:00 https://sqlines.com/sql-server-to-oracle/select_asterisk?rev=1736606587&do=diff In SQL Server, you can use SELECT * with other columns or expressions without specifying the table name or alias. Oracle requires that the table name or an alias are specified for * if it is used with other columns or expressions. SQL Server: -- Standalone * SELECT * FROM colors; /* Ok */ -- With other columns without alias SELECT *, name FROM colors; /* Ok */ text/html 2023-06-25T15:44:09+00:00 https://sqlines.com/sql-server-to-oracle/str?rev=1687707849&do=diff In SQL Server you can use STR function to convert a decimal number to string specifying the total number of digits as well as fractional part and its rounding. In Oracle, you can use TO_CHAR function with the corresponding format. SQL Server: -- Leave 1 fractional digit and round the number SELECT STR(123.45, 6, 1); # 123.5 -- Truncate the fractional part and round the number SELECT STR(123.7, 6); # 124 text/html 2023-05-24T13:57:33+00:00 https://sqlines.com/sql-server-to-oracle/stuff_select_for_xml?rev=1684936653&do=diff In SQL Server, STUFF with SELECT FOR XML PATH statement is widely used to concatenate strings from multiple rows into a single row value. 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');