Conversion Errors in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Conversion Errors in Stored Procedure

Author

Topic

kvasire
Starting Member

5 Posts

Posted - 2011-12-07 : 10:20:49

Hi,

I am facing two errors in two different stored procedures:

ALTER PROCEDURE [dbo].[sda_bld_forecast_drive](@p_fcst_start_year char(4),
@p_fcst_start_month int,
@p_fcst_end_year char(4))
AS
BEGIN

declare @fcst_start_dt smalldatetime,
@fcst_end_dt smalldatetime,
@the_dt smalldatetime,
@i int

select @fcst_start_dt=cast(cast(@p_fcst_start_month as char) + '/1/' + @p_fcst_start_year as smalldatetime)

Conversion failed when converting character string to smalldatetime data type.

ALTER PROCEDURE [dbo].[pa_bld_ofa_plan](@p_scenario varchar(20),
@p_start_year varchar(4),
@p_start_month int,
@p_num_months int)

declare @rep_year int,
@rep_month int,
@num_month int,
@category varchar(20),
@sql varchar(4000),
@max_year int, @end_year int, @temp_year int,
@max_month int, @end_month int

select @end_year=datepart(yyyy,DATEADD( m , @p_num_months, cast(@p_start_month as char) + '/1/' + cast(@p_start_year as char) ) )
select @end_month=datepart(m,DATEADD( m , @p_num_months, cast(@p_start_month as char) + '/1/' + cast(@p_start_year as char) ) )

Conversion failed when converting date and/or time from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 10:26:30

you just need this

select @fcst_start_dt=dateadd(mm,@p_fcst_start_month-1,@p_fcst_start_year)

instead of what you've now

similarly change others also

select @end_year=datepart(yyyy,DATEADD( m , @p_num_months, dateadd(mm,@p_fcst_start_month-1,@p_fcst_start_year) ) )
select @end_month=datepart(m,DATEADD( m , @p_num_months, dateadd(mm,@p_fcst_start_month-1,@p_fcst_start_year)) )
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 10:27:36

make sure you read this to understand why you're getting this error

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvasire
Starting Member

5 Posts

Posted - 2011-12-07 : 11:32:57

Still facing the same error with the suggested changes..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 11:55:44

quote:
Originally posted by kvasire

Still facing the same error with the suggested changes..

do similar changes in all places where you're using datevalues. I've shown parts inside posted code only. I dont know where else you've used similar logic so make sure you change everywhere
also whereever you're passing date values pass them in YYYYMMDD format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvasire
Starting Member

5 Posts

Posted - 2011-12-07 : 12:00:37

I am not using the date values anywhere else in the code. I am just passing the year and month as parameters.
Sample values:
'2011'
10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 12:03:45

which procedure currently throws this error? sda_bld_forecast_drive or pa_bld_ofa_plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvasire
Starting Member

5 Posts

Posted - 2011-12-07 : 12:11:28

Both are throwing the errors..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 12:19:50

quote:
Originally posted by kvasire

Both are throwing the errors..

I'm sure its not in posted part
as i've tried your posted procedures with my changes and its working fine for me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvasire
Starting Member

5 Posts

Posted - 2011-12-07 : 12:59:21

Thank you so much for help.

This Date functions are very confusing. I am posting the entire code here:

ALTER PROCEDURE [dbo].[sda_bld_forecast_drive](@p_fcst_start_year char(4),
@p_fcst_start_month int,
@p_fcst_end_year char(4))
AS
BEGIN

declare @fcst_start_dt smalldatetime,
@fcst_end_dt smalldatetime,
@the_dt smalldatetime,
@i int

--select @fcst_start_dt=cast(cast(@p_fcst_start_month as char) + '/1/' + @p_fcst_start_year as smalldatetime)

select @fcst_start_dt=dateadd(mm,@p_fcst_start_month-1,@p_fcst_start_year)

--build table dbo.sda_driver_sponsor_forecast
Delete from dbo.sda_driver_sponsor_forecast
-- get the 2 month from ytd.
select *
into #temp_olddata
from dbo.sda_driver_sponsor_ytd
where Year + RIGHT('00' + LTRIM(Month),2)
--cast(year as int) *100+ month
between cast(datepart(yyyy,dateadd(m,-3,@fcst_start_dt)) as int) * 100 + datepart(m,dateadd(m,-3,@fcst_start_dt))
and cast(datepart(yyyy,dateadd(m,-2,@fcst_start_dt)) as int) * 100 + datepart(m,dateadd(m,-2,@fcst_start_dt))

--get current month from tpm
insert into #temp_olddata
SELECT
year,
month,
company_reciever,
cost_ctr_reciever,
driver_id,
driver_measure,
div_spon_percentage
FROM dbo.tpm_driver_sponsor WHERE
scenario = 'ACTUAL' and
year = datepart(yyyy,dateadd(m,-1,@fcst_start_dt)) AND
month = datepart(m,dateadd(m,-1,@fcst_start_dt))

--get ave from about 3 month
SELECT distinct
month,
a.driver_id
into #temp_mon_drv
FROM #temp_olddata a,
dbo.tpm_driver_sponsor_forecast_ctl b
WHERE a.driver_id like b.driver_id

select driver_id, count(month) num_month
into #temp_drv
from #temp_mon_drv
group by driver_id

SELECT
company_reciever,
cost_ctr_reciever,
a.driver_id,
sum(div_spon_percentage)/max(num_month) div_spon_percentage,
sum(driver_measure)/max(num_month) driver_measure
into #temp_driver_sponsor_forecast
FROM #temp_olddata a,
dbo.tpm_driver_sponsor_forecast_ctl b,
#temp_drv c
WHERE
a.driver_id like b.driver_id
and a.driver_id=c.driver_id
group by company_reciever,
cost_ctr_reciever,
a.driver_id

--get all others from actual
insert into #temp_driver_sponsor_forecast
SELECT
a.company_reciever,
a.cost_ctr_reciever,
a.driver_id,
a.div_spon_percentage,
a.driver_measure
FROM dbo.tpm_driver_sponsor a left join
dbo.#temp_driver_sponsor_forecast b
on ( a.driver_id=b.driver_id)
WHERE
scenario = 'ACTUAL' and
year = datepart(yyyy,dateadd(m,-1,@fcst_start_dt)) and
month= datepart(mm,dateadd(m,-1,@fcst_start_dt)) and
b.driver_id is null

-- expand them to end of forecast date
--select @fcst_start_dt=cast(cast(@p_fcst_start_month as char) + '/1/' + @p_fcst_start_year as smalldatetime)
select @fcst_end_dt=cast('12/1/' + @p_fcst_end_year as smalldatetime)

--select @fcst_start_dt
--select @fcst_end_dt

select @the_dt=@fcst_start_dt

WHILE (@the_dt <= @fcst_end_dt)
BEGIN
insert into dbo.sda_driver_sponsor_forecast
select datepart(yyyy,@the_dt),
datepart(mm,@the_dt),
company_reciever,
cost_ctr_reciever,
driver_id,
div_spon_percentage,
driver_measure
from #temp_driver_sponsor_forecast

select @the_dt=dateadd(m,1,@the_dt)

--select @the_dt
END

--build table dbo.sda_driver_project_forecast
--get current data
select
project_id,
driver_id,
proj_div_percentage
into #temp_project
from dbo.tpm_driver_project
where scenario='ACTUAL' and
year = datepart(yyyy,dateadd(m,-1,@fcst_start_dt)) and
month= datepart(mm,dateadd(m,-1,@fcst_start_dt))

END

GO

Here is the execute statement i am using:

DECLARE @return_value int

EXEC @return_value = [dbo].[sda_bld_forecast_drive]
@p_fcst_start_year = N'''2011''',
@p_fcst_start_month = 10,
@p_fcst_end_year = N'''2012'''

SELECT 'Return Value' = @return_value

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 13:33:48

i see lot of places again with the datepart logic. change all of them based on my modified logic and then error should go. I dont have time to go through the entire code and fix the issues.
Go through below links and understand how sql server interprets date values

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html
http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources