Monday, September 16, 2013

SQL Server Function Date Range Example

The function below retrieves the top Freight amount specified by date range values
 use CompanySales  
 IF object_id (N'dbo.GetTopFreightValueRange', N'FN') IS NOT NULL  
 drop function GetTopFreightValueRange  
 go  
 create function GetTopFreightValueRange  
 (  
   @startDate nvarchar(50), @endDate nvarchar(50)  
 )  
 returns decimal(10,2)   
 as  
 begin   
 declare @result varchar(50)   
      set @result =   
      (  
            select FreightAmount   
                 from dbo.Orders  
            where CONVERT(VARCHAR(10), ShippedDate, 120) >= @startDate  
                  and  
                  CONVERT(VARCHAR(10), ShippedDate, 120) <= @endDate  
      )  
 return @result  
 end  
 go  
-- select dbo.GetTopFreightValueRange('1996-11-01','1996-11-30') as freight_value;

0 comments:

Post a Comment