Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Unanswered: Simple Stored Procedure Question

    Hi,

    What I am trying to do is create a stored procedure that will take the value of the GetDate() function (Current Date) and manipulate the date and return a value in Varchar(10).

    The stored procedure is set up to give me the monday date regardless of what day it is in the week. I ALREADY have a function that does this for me, but I also NEED to have this in a stored procedure. The preceding sentence is non-negotiable .

    Unfortunately I can't get the stored procedure to accept the GetDate() value. The object is to get this stored procedure called from an SSIS ETL package.

    The following is the SP:

    Code:
    ALTER PROCEDURE [dbo].[spCognosFileName] 
    -- Add the parameters for the stored procedure here
    	@CurrentDate DateTime
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	Declare @ConvertedFormat DateTime
    
    	Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)
    		+ dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)
    
    Return
    		
    	Select Convert(Varchar(10),@ConvertedFormat,112) + '.txt'
    	
    END
    Any help would be appreciated.
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not have time to ask why but try this...

    Code:
    CREATE PROCEDURE [dbo].[spCognosFileName]
    (
    @CurrentDate DateTime
    )	
    AS
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON
    	
    	Declare @ConvertedFormat DateTime
    
    	Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)
    		+ dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)
    
    
    		
    	Select Convert(Varchar(10),@ConvertedFormat,112) + '.txt'
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    DROP PROCEDURE [dbo].[pCognosFileName] 
    go
    
    CREATE PROCEDURE [dbo].[pCognosFileName] (
    -- Add the parameters for the stored procedure here
    	@CurrentDate DateTime,
    	@MondayDateStr CHAR(12) OUTPUT
    )
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	Declare @ConvertedFormat DateTime
    
    	Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)
    		+ dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)
    
    	SET @MondayDateStr =  Convert(Varchar(10),@ConvertedFormat,112) + '.txt'
    	
    END
    go
    
    DECLARE @MyCurrentDate DATETIME
    DECLARE @MyReturnValue CHAR(12)
    
    SET @MyCurrentDate = '2009.08.25'
    SET @MyCurrentDate = GetDate()
    EXECUTE dbo.pCognosFileName @CurrentDate = @MyCurrentDate, 
    			@MondayDateStr = @MyReturnValue output
    
    select @MyReturnValue
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Aug 2009
    Posts
    18

    Stored Proc -- Wim

    Thanks a bunch, Wim.

    Really appreicate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •