CHILD_RECORD_FOUND Equivalent in SQL Server

HI All..,,

for CHILD_RECORD_FOUND in Oracle , in SQL Server is Error_number = 547


begin try
---------
----------
end try
 begin catch 

     if @errornumber = 547
     print 'Unable to delete Parent , as child record exists'
 end catch



for 547 ===> delete statement conflicted with the reference constraint

LOCKS Information in SQL Server

http://www.informit.com/articles/article.aspx?p=327992&seqNum=8

Extract Time from given Date in SQL Server

Hi guys,

To extract the exact time from from given date as shown below:


Select cast(getdate() as time) [time]

How to search a word contains procedure in SQL Server...

SELECT *
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%test%';

RAISEERROR in SQL Server......

declare @ErrorMessage varchar(1000) = error_message()

declare @ErrorSeverity int =error_severity()

declare @ErrorState int = error_state()

RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );  

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