Search This Blog

Thursday, June 25, 2015

How can calculate hours in sql query ?

I want that data from my table which have passed 48 hours from entry time.

Solution:


select name,lastname,address,city,country,mobile,dbo.udf_GetSimpleDate(entrydate) as entrydate from tab_cases where  (DATEDIFF(hh,getdate(),entrydate)=-48)


Using function for date format : udf_GetSimpleDate(entrydate)

CREATE FUNCTION [dbo].[udf_GetSimpleDate] ( @pDate    DATETIME )                  
RETURNS varchar(100)                  
AS                  
BEGIN                  
 declare @returndate varchar(100)            
 set  @returndate= right('00' + DATENAME(DD,@pDate),2) + '-' + right('00' + convert(varchar(2),datepart(MM,@pDate)),2) + '-' +DATENAME(YY, @pDate)                
 if datediff(d,@pDate,getdate())>10000      
 begin        
  set @returndate=''        
 end        
 return @returndate            
         
END  

No comments :