Compare common functions on SQL Server, Oracle, and DB2!

1. Character functions:

The characters in SQL Server are case-insensitive.

However, the lower and upper functions are common in SQL Server, Oracle, and DB2. For example, select empno, ename, deptno from EMP where upper (ename) = upper ('bucket ');

 

Example of connection characters:

• SQL SERVER: Select 'good '+ 'string'

• ORACLE: Select Concat ('good', 'string') from dual;

• DB2: Select Concat ('good', 'string') from sysibm. sysdummy1; function example for manipulating characters: Oracle and DB2 are written as follows: Select ename, Concat (ename, job), length (ename), instr (ename, 'A ') from EMP where

Substr (job,) = 'sales'; equivalent Syntax of SQL SERVER: Select ename, (ename + job), Len (ename ), patindex ('% A %', ename) from EMP where substring (job, 1, 5) = 'sales '; 2. Date functions:Take the current date: SQL SERVER: Select getdate () Oracle: Select sysdate from dual; DB2:

Select current date from sysibm. sysdummy1; examples of arithmetic operators used in the last period: Select ename, (sysdate-hiredate)/7 weeks from EMP where deptno = 10; SQL Server Syntax: select ename, datediff (day, hiredate, getdate ()/7 weeks
From EMP where deptno = 10;
Note: • datepart () function: returns the specified part of the time in integer form. • Usage: datepart (datepart, date) • parameter description: the part of the time to be returned for datepart. Commonly Used values include year, month, day, hour, and minute. • Date is the specified time. • Example: • Select datepart (month, getdate () as 'month number' • Select datepart (day, getdate () as 'month number' • datediff () function: returns the difference between the two time ranges based on the specified time range. Returns an integer. For example, the difference between and is calculated by day • 9 days. The difference between and is one year, and the difference is calculated by year. The difference between and is nine months. • Usage: datediff (darepart, date1, date2) • parameter description: datepart (same as above) • date1, date2 (same as date) • Example: • Select datediff (month, '2017-6-12 ', '2017-6-21 ') as a • Select datediff (day, '2017-6-12', '2017-6-21 ') as a DB2 statement: Select ename, (sysdate-hiredate)/7 weeks from EMP where deptno = 10; sysdate on DB2 has a timestamp. Cannot be used for computing. But the statement does not report an error. The returned result is an error. Please note! Correct Method: Select ename, (days (current date)-days (date (hiredate)/7 weeks from EMP where deptno = 10; 3. conversion functions:Example of conversion from date to character on SQL SERVER: Select ename, convert (char (10), hiredate) hiredate from EMP; or: Select ename, cast (hiredate as char (10 )) example of conversion from date to date in Oracle: Select ename, to_char (hiredate, 'fmdd month yyyy ') hiredate from EMP; example of conversion from date to character in DB2: select char (current date) from sysibm. sysdummy1; select char (current time) from sysibm. sysdummy1;