January 9, 2011 at 11:57 pm
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
January 10, 2011 at 12:20 am
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
January 11, 2011 at 11:32 am
So what you are saying is his coding stinks and he should buy your books?
January 12, 2011 at 3:16 am
Hey kalit.sikka
Tnx for your follow up post on ur resolution.
January 12, 2011 at 9:04 am
churlbut (1/11/2011)
So what you are saying is his coding stinks and he should buy your books?
No, Joe is simply pointing out that he is a small-minded person that has no business being on the SQL Server Central forums, just like he has no business on any other SQL Server forum in existence. The vast majority of his posts are nothing more than denigrating others and spouting crap that 99.x% of SQL Server users don't care about nor need to know.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2011 at 2:12 pm
Thanks for the clarification Kevin, I would definitely agree with your assessment.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply