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

Comments (0)

Post a Comment