Skip to content

Frequently Asked Questions - Oracle Functions in SQL Server

What's the equivalent of Oracle's NVL function in SQL Server?

Oracle's NVL function takes two arguments:

NVL( a1, a2 )

where a1 and a2 are expressions. The NVL function returns a2 if a1 is NULL. If a1 is not NULL then a1 is returned.

The equivalent of this function in SQL Server is the ISNULL function. Similar to the NVL function, the ISNULL function takes two arguments:

ISNULL ( a1, a2 )

where a1 is the expression to be checked for NULL and a2 is the expression to be returned if a1 is NULL. If a1 is not null then a1 is returned.

What's the equivalent of Oracle's INITCAP function in SQL Server?

Oracle's INITCAP function takes two arguments:

INITCAP ( a1 )

where a1 is a character string. The INITCAP function returns a1 with the first character of each word in uppercase and all others in lowercase. Words are delimited by white space, control characters and punctuation symbols.

There's no equivalent function in SQL Server for Oracle's INITCAP function. But its functionality can easily be simulated in SQL Server and one such function can be found in the following link:

User-Defined Functions - InitCap / String Capitalization

What's the equivalent of Oracle's SYSDATE function in SQL Server?

Oracle's SYSDATE functions takes no arguments and returns the current date and time to the second.

SELECT SYSDATE AS [CurrentDate] FROM DUAL;

The equivalent of Oracle's SYSDATE function in SQL Server is the GETDATE() function.

SELECT GETDATE() AS [CurrentDate]

The GETDATE() function returns the current date and time to the millisecond.

What's the equivalent of Oracle's NVL2 function in SQL Server?

Oracle's NVL2 function is a variation of the NVL function. The NVL2 function takes three arguments:

NVL2 ( arg1, arg2, arg3 )

where arg1, arg2 and arg3 are expressions. NVL2 returns arg3 if arg1 is NULL, and arg2 if arg1 is not NULL. The NVL function allows you to perform some value substitution for NULL values while the NVL2 function allows you to implement an IF..THEN...ELSE construct based on the nullity of data.

The equivalent of Oracle's NVL2 function in SQL Server is the CASE statement.

CASE WHEN arg1 IS NOT NULL THEN arg2 ELSE arg3 END

Similar to the NVL2 function, this CASE statement will return arg3 if arg1 is NULL, and arg2 if arg1 is not NULL.

What's the equivalent of Oracle's LAST_DAY function in SQL Server?

Oracle's LAST_DAY function takes a single argument:

LAST_DAY ( datearg )

where datearg is a date. This function returns the last day of the month for the given date datearg.

The equivalent of Oracle's LAST_DAY function in SQL Server is the EOMONTH() function.

What's the equivalent of Oracle's CONCAT function in SQL Server?

Oracle's CONCAT function takes two arguments:

CONCAT ( s1, s2 )

where s1 and s2 are both character string values. The function returns s2 appended or concatenated to s1. If s1 is NULL, then s2 is returned. If s2 is NULL, then s1 is returned. If both s1 and s2 are NULL, then NULL is returned. The CONCAT function returns the same results as using the Oracle's string concatenation operator: s1 || s2.

There's no equivalent function in SQL Server for Oracle's CONCAT string function. What SQL Server has is the string concatenation operator, the plus sign (+). To concatenate strings in SQL Server, simply add the string values together using the plus sign: s1 + s2

But there's a difference in behavior of concatenating strings between Oracle and SQL Server. As mentioned above, when concatenating strings in Oracle, if one of the string values is NULL, the string value is treated as an empty string. However in SQL Server, when concatenating strings, if one of the string values is NULL the result may be different depending on the setting of the CONCAT_NULL_YIELDS_NULL option.

If the CONCAT_NULL_YIELDS_NULL option is to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. If the CONCAT_NULL_YIELDS_NULL option is set to OFF, the NULL value is treated as an empty character string.

To avoid getting a NULL value when concatenating strings regardless of the setting of the CONCAT_NULL_YIELDS_NULL option, you can use the ISNULL function to change any NULL value to an empty string:

ISNULL(s1, '') + ISNULL(s2, '')

What's the equivalent of Oracle's DECODE function in SQL Server?

Oracle's DECODE function can use multiple arguments:

DECODE ( exp1, val1, ret1 [, val2, ret2] [, default]

exp1 is an expression. val1 is a matching expression to compare with exp1. If val1 is equivalent to exp1, then ret1 is returned; otherwise, additional matching expressions (val2, val3, val4, and so on) is returned. If no match is found and the default expression default is included, then default is returned.

The equivalent of Oracle's DECODE function in SQL Server is the CASE function.

CASE input_expression
    WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2 
    WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

This is the simple format of the CASE function, which compares an expression (input_expression) to a set of simple expressions (when_expression_n) to determine the result (result_expression_n).

input_expression is the expression evaluated when using the simple CASE format. WHEN when_expression is a simple expression to which input_expression is compared. THEN result_expression is the expression returned when input_expression equals when_expression evaluates to TRUE. ELSE else_result_expression is the expression returned if no comparison evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, the CASE function returns a NULL value.

What's the equivalent of Oracle's ADD_MONTHS function in SQL Server?

Oracle's ADD_MONTHS function takes two arguments:

ADD_MONTHS ( d, i )

where d is a date and i is an integer. This function returns the date d plus i months. If i is a decimal number, the function will implicitly convert it to an integer by truncating the decimal portion (for example, 4.6 becomes 4). i can also be negative and it will subtract that number of months to the date d.

The equivalent of Oracle's ADD_MONTHS function in SQL Server is the DATEADD date function. The DATEADD date function returns a new datetime value based on adding an interval to the specified date. The DATEADD date function takes three parameters:

DATEADD ( datepart, number, date )

The datepart parameter specifies on which part of the date to return a new value. In our case, we will be using MONTH, MM or M because we only want to add or subtract months to the date.

The number parameter is the value used to increment datepart. If you specify a decimal value, the fractional/decimal part of the value is discarded. For example, if you specify MONTH for datepart and 2.85 for number, date is incremented by 2 months. This has the same behavior as the ADD_MONTHS function.

The date parameter is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

What's the equivalent of Oracle's EXTRACT function in SQL Server?

Oracle's EXTRACT function, which is only available starting from Oracle 9i, extracts and returns the specified component comp of date/time or interval expression dt:

EXTRACT ( comp FROM dt )

The valid components are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION and TIMEZONE_ABBR. The specified component must exist in the expression. So, to extract a TIMEZONE_HOUR, the date/time expression must be a TIMESTAMP WITH TIMEZONE data type.

The equivalent of Oracle's EXTRACT function in SQL Server is the DATEPART date function. The DATEPART function accepts two arguments and returns an integer representing the specified date part of the specified date:

DATEPART ( datepart, date )

datepart is the parameter that specifies the part of the date to return. The valid datepart values are YEAR (or YY, YYYY), QUARTER (or QQ, Q), MONTH (or MM, M), DAYOFYEAR (or DY, Y), DAY (or DD, D), WEEK (or WK, WW), WEEKDAY (or DW), HOUR (or HH), MINUTE (or MI, N), SECOND (or SS, S) and MILLISECOND (or MS).

date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

What's the equivalent of Oracle's MONTHS_BETWEEN function in SQL Server?

Oracle's MONTHS_BETWEEN function takes two arguments:

MONTHS_BETWEEN ( date1, date2 )

where date1 and date2 are both dates. This function returns the number of months that date2 is later than date1. A whole number is returned if date1 and date2 are the same day of the month or if both dates are the last day of a month.

The equivalent of Oracle's MONTHS_BETWEEN function in SQL Server is the DATEDIFF function. The DATEDIFF function returns the number of date and time boundaries crossed between two specified dates and it takes 3 arguments:

DATEDIFF ( datepart, startdate, enddate )

datepart is the parameter that specified on which part of the date to calculate the difference. In our case, we will be passing the value MONTH, MM or M.

startdate is the beginning date for the calculation and it is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

enddate is the ending date for the calculation and is an expression that returns a datetime or smalldatetime value, or a character string in a date format.