Previous Month First day in SQL Server

Previous Month First day...

CONVERT(DATETIME, CONVERT(VARCHAR(7), dateadd(m,-1,GETDATE()), 120) + '-01')

Extract Day, Month, Year from date

Hi guys..,

Oracle	SQL Server
SELECT to_char(sysdate,'dd') FROM DUAL;	select day(getdate())
SELECT to_char(sysdate,'mm') FROM DUAL;	select month(getdate())
SELECT to_char(sysdate,'yyyy') FROM DUAL;	select year(getdate())

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()