conversion error on Stored Procedure

[gavinjb]

Hi,

I have writen a SP, but when running it I get the following error.

Msg 245, Level 16, State 1, Procedure ExpPeriodsForYear, Line 47
Conversion failed when converting the varchar value '01-Oct-' to data type int.

The SP is

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gavin Blackford
-- Create date: 29-Nov-2006
-- Description:	SP to work out the amount of Periods for current year
-- =============================================
ALTER PROCEDURE ExpPeriodsForYear
	-- Add the parameters for the stored procedure here
	@ModuleCode varchar,
	@ForYear Int,
	@StartDate datetime,
	@ExpEndDate datetime,
	@NoWeeks int,
	@FundedBy varchar,
	@ExpPeriods int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
	IF @ModuleCode LIKE 'Q%'
		BEGIN
			IF @NoWeeks < 20	
				BEGIN
					SET @ExpPeriods = 1 --average Qs
				END
			ELSE IF @NoWeeks < 36
				BEGIN
					SET @ExpPeriods = 2 -- average Q's
				END
			ELSE IF @NoWeeks % 52 = 0 -- 1 Year courses (52 calendar weeks)
				BEGIN
					SET @ExpPeriods = (@NoWeeks/52)*3
				END
			ELSE
				BEGIN
					SET @ExpPeriods = @NoWeeks / 12 --old calc (rounded down)
				END
		END

	IF @ExpPeriods > 3 
		BEGIN
			SET @ExpPeriods = 3
		END

IF (@StartDate >= Convert(DateTime, '01-Aug-' + Convert(VarChar, @ForYear)) AND
	@StartDate <= Convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear))) OR
	(@StartDate > Convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear)) AND
	@ExpEndDate < Convert(DateTime, '01-Jan-' + Convert(VarChar,(@ForYear+1)))) OR
	(@StartDate > Convert(DateTime, '15-May-' + Convert(VarChar, @ForYear)) AND
	@StartDate < Convert(DateTime, '01-Aug-' + Convert(VarChar, @ForYear)) AND
	@ExpEndDate >= Convert(DateTime, '01-Aug-' + Convert(VarChar, @ForYear))) Or
	@StartDate <= convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear)) AND
	@ExpEndDate >= Convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear)) 
	BEGIN
		SET @ExpPeriods = @ExpPeriods + 1
	END

IF (@StartDate >= Convert(DateTime, '01-Jan-' + Convert(VarChar, (@ForYear+1))) AND
	@StartDate <= Convert(DateTime, '01-Feb-' + Convert(VarChar, (@ForYear+1)))) OR
	(@StartDate > Convert(DateTime, '01-Feb-' + Convert(VarChar, @ForYear+1)) AND
	@ExpEndDate < Convert(DateTime, '01-May-' + Convert(VarChar, (@ForYear+1)))) OR
	(@StartDate > Convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear)) AND
	@StartDate < Convert(DateTime, '01-Jan-' + Convert(VarChar, (@ForYear+1))) AND
	@ExpEndDate >= Convert(DateTime,'01-Jan-' + Convert(VarChar, (@ForYear+1)))) OR
	@StartDate <= Convert(DateTime, '01-Feb-' + Convert(VarChar, (@ForYear+1))) AND
	@ExpEndDate >= convert (DateTime, '01-Feb-' + Convert(VarChar, @ForYear))
	BEGIN
		SET @ExpPeriods = @ExpPeriods + 1
	END

IF (@StartDate >= Convert(DateTime, '01-May-' + Convert(VarChar, (@ForYear+1))) AND
	@StartDate <= Convert(DateTime, '15-May-' + Convert(VarChar, (@ForYear+1)))) OR
	(@StartDate > Convert(DateTime, '01-May-' + Convert(VarChar, (@ForYear+1))) AND
	@ExpEndDate < Convert(DateTime, '01-Aug-' + Convert(VarChar, @ForYear))) OR
	(@StartDate > Convert(DateTime, '01-Feb-' + Convert(VarChar, (@ForYear+1))) AND 
	@StartDate < Convert(DateTime, '01-May-' + Convert(VarChar, (@ForYear+1))) AND
	@ExpEndDate >= Convert(DateTime, '01-May-' + Convert(VarChar, (@ForYear+1)))) OR
	(@StartDate <= Convert(DateTime, '15-May-' + Convert(VarChar, (@ForYear+1))) AND
	@ExpEndDate >= Convert(DateTime, '15-May-' + Convert(VarChar, @ForYear)))
	BEGIN
		SET @ExpPeriods = @ExpPeriods + 1
	END
END
GO

Does anyone have any ideas how I can resolve this

Thanks,

Gavin,

 

[pdx_man]

Not sure ...

This works fine for me, does it for you?

declare @ForYear Int

select @ForYear = '06'
select Convert(DateTime, '01-Oct-' + Convert(VarChar, @ForYear))

What is your call to this proc?