Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Problem in stored procedure

    Getting Error:
    Msg 241, Level 16, State 1, Procedure GetMaxCostCenterValue, Line 15
    Conversion failed when converting date and/or time from character string.

    Stored Procedure:

    ALTER PROCEDURE GetMaxCostCenterValue
    (
    @CostCenter nvarchar(225),
    @DateFrom datetime,
    @DateTo datetime
    )
    AS
    SET NOCOUNT ON
    BEGIN

    DECLARE @Query NVARCHAR(525)
    DECLARE @pn_CostCenter NVARCHAR(525)


    SET @Query = N'SELECT
    dbo.Formatdate(m.Month) As Financial_Month
    , d.Dept_name
    ,m.'+@CostCenter+'
    FROM
    tblMasterTable m
    INNER JOIN tbldept d
    ON d.Dept_ID = m.dept
    Where
    '+@CostCenter+' =
    (SELECT MAX('+@CostCenter+') from tblMasterTable)
    AND m.Month between '+@DateFrom+' and '+@DateTo+''

    Print(@DateFrom)
    Print(@DateTo)
    PRINT(@Query)

    EXECUTE sp_executesql @Query, N'@CostCenter varchar(255), @DateFrom datetime, @DateTo datetime',
    @CostCenter, @DateFrom, @DateTo


    END

    -----

    EXECUTING SP

    DECLARE @DateTo DATETIME
    DECLARE @DateFrom DATETIME

    SET @DateTo = GETDATE()
    SET @DateFrom = DATEAdd(DAY,-221, @DateTo)


    EXEC GetMaxCostCenterValue NetProfit, @DateFrom, @DateTo

  2. #2
    Join Date
    Jul 2010
    Posts
    4

    Problem solved

    ALTER PROCEDURE GetMaxCostCenterValue
    (
    @CostCenter nvarchar(225),
    @DateFrom datetime,
    @DateTo datetime
    )
    AS
    SET NOCOUNT ON
    BEGIN

    DECLARE @Query NVARCHAR(525)
    DECLARE @pn_CostCenter NVARCHAR(525)


    SET @Query = N'SELECT
    dbo.Formatdate(m.Month) As Financial_Month
    , d.Dept_name
    ,m.'+@CostCenter+'
    FROM
    tblMasterTable m
    INNER JOIN tbldept d
    ON d.Dept_ID = m.dept
    Where
    '+@CostCenter+' =
    (SELECT MAX('+@CostCenter+') from tblMasterTable)
    AND m.Month between '''+cast(@DateFrom as varchar(101))+''' and '''+cast(@DateTo as varchar(101))+''''

    Print(@DateFrom)
    Print(@DateTo)
    PRINT(@Query)

    EXECUTE sp_executesql @Query, N'@CostCenter varchar(255), @DateFrom datetime, @DateTo datetime',
    @CostCenter, @DateFrom, @DateTo


    END

Posting Permissions

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