Getting From Date and To Date in Date List
--EXEC sp_Myforeach_Date @SatrtDate='01/03/2010',@EndDate='03 Mar 2010',@DatePart='dd',@OutPutFormat=101

CREATE PROCEDURE [dbo].[sp_Myforeach_Date] 
    -- Add the parameters for the stored procedure here 
    @SatrtDate as DateTime, 
    @EndDate as dateTime, 
    @DatePart as varchar(2), 
    @OutPutFormat as int  
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
   
@DateList Table 
    (Date varchar(50)) 
 
    WHILE @SatrtDate<= @EndDate 
    BEGIN 
    INSERT @DateList (Date) values(Convert(varchar,@SatrtDate,@OutPutFormat)) 
    IF Upper(@DatePart)='DD' 
    SET @SatrtDate= DateAdd(dd,1,@SatrtDate) 
    IF Upper(@DatePart)='MM' 
    SET @SatrtDate= DateAdd(mm,1,@SatrtDate) 
    IF Upper(@DatePart)='YY' 
    SET @SatrtDate= DateAdd(yy,1,@SatrtDate) 
    END  
    SELECT * FROM @DateList 
END 
03:30 | Category: | 0 comments

Get Hours
/******************************************************************************
Purpose     : Get the Total hours with Minutes
Returns     : Decimal value
Arguments   : select dbo.UNF_GetHours(getdate(), getdate()+4)
===============================================================================Created By  : Suresh Kumar N
Created On  : 15-Mar-2011     
-------------------------------------------------------------------------------
Modification Log
-------------------------------------------------------------------------------
Modified By       Modified Date       Reason
-------------------------------------------------------------------------------

******************************************************************************/

create FUNCTION [dbo].[fnGetHours](@StartDate DATETIME, @EndDate DATETIME) 
RETURNS DECIMAL(8,2) 
AS 
BEGIN 
       DECLARE @TotalMinutes AS DECIMAL
       DECLARE @HoursWithoutSeconds AS INT
       DECLARE @Totalhours AS DECIMAL(8,2)
       -- Get the Total Minutes
       SELECT @TotalMinutes = CAST(DATEDIFF(mi,@StartDate,@EndDate)AS DECIMAL)
       -- Get the Hours without remaining Minutes
       select @HoursWithoutSeconds = FLOOR(CAST(@TotalMinutes AS DECIMAL)/60.0)
       --Calculate the Total hours with Minutes
       select @Totalhours =
              CAST(@HoursWithoutSeconds AS DECIMAL)
              +
              ((@TotalMinutes - (CAST(@HoursWithoutSeconds AS DECIMAL) * 60.0))/100)

 RETURN @Totalhours 
END 




03:57 | Category: | 0 comments

TrimAll Function
/*********************************************************************************************************************
Purpose : Trim the given input string
Returns : Trimed string
Arguments : select * from fnTableSplitString('ab~cde~~efgh~','~')
=====================================================================================================================
Created By : Suresh Kumar N
Created On : 29-Mar-2011
----------------------------------------------------------------------------------------------------------------------
Modification Log
----------------------------------------------------------------------------------------------------------------------
Modified By Modified Date Reason
----------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************/ CREATE FUNCTION [dbo].[fnTrim](@InputString nvarchar(max))RETURNS VARCHAR(MAX)AS
BEGINDECLARE @Output AS VARCHAR(MAX)SELECT @Output = LTRIM(RTRIM(@InputString))RETURN @OutputENDGO
03:31 | Category: | 0 comments