Oracle's Median in SQL Server

Hi All...,

Here is the equivalent function for Oracle's Median in SQL Server..

Oracle	SQL Server
select deptno,median(sal) from emp group by deptno	select * from (select distinct e.deptno, PERCENTILE_CONT(0.5)  within group(order by e.sal) over(partition by e.deptno)  as median_sal from emp e)  as a   where a.deptno is not null

TRUNC on DATE in Oracle to SQL SERVER

Hi all ..,


Here is the date formats, conversions to SQL Server..

Oracle	SQL Server
SELECT TRUNC(SYSDATE) FROM dual;	CONVERT(DATETIME, CONVERT(DATE, GETDATE()))
Select TRUNC(SYSDATE, 'MM') from dual;	Select CONVERT(DATETIME, CONVERT(VARCHAR(7), GETDATE(), 120) + '-01')
Select TRUNC(SYSDATE, 'MONTH') from dual;	Select CONVERT(DATETIME, CONVERT(VARCHAR(7), GETDATE(), 120) + '-01')
SELECT TRUNC(SYSDATE, 'YY') FROM dual;	SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), GETDATE(), 120) + '-01-01');

Equivalent of CURRENT_TIMESTAMP of Oracle in SQL Server

Hi All..,

The Equivalent of CURRENT_TIMESTAMP of Oracle in SQL Server is...

Oracle	SQL Server
Select CURRENT_TIMESTAMP from dual;	SELECT sysdatetimeoffset()

Adding days in SQL Server

Hi all,

Here is the function to add number of days to a date.

Oracle	SQL Server
Select sysdate+20 from dual	Select dateadd(day,20,getdate())

Equivalent of ROWNUM of Oracle in SQL Server

Hi all.,

Here is the equivalent of ROWNUM of Oracle in SQL Server

Oracle	SQL Server
Select * from emp where rownum= 1	Select top 1 from emp