error in sp - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

error in sp

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-05 : 02:19:49

hi

am getting the error if i run the sp
in the sp i declared 3 var
DECLARE @year INT
DECLARE @month INT
DECLARE @monthName VARCHAR(50)
and am getting error for the last 2 var that is @month and @monthname

error::
Adding a value to a 'datetime' column caused overflow.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 02:30:11

The error says the fault is generated on another line of code.
Please post full code for SP and also the full error text.

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-05 : 02:35:59

PROCEDURE [dbo].[Rework] (@Fromyear INT
,@Frommonth INT
,@ToYear INT
,@ToMonth INT
,@ReportType VARCHAR(50)
,@ProjectSeqNo INT
,@ModuleIds VARCHAR(8000))
AS

BEGIN

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = cast(dateadd((mm,(@Fromyear-1900)* 12 + @Frommonth - 1,0) + (1-1)) ,datetime)
SET @EndDate = cast(dateadd((mm,(@Toyear-1900)* 12 + @Tomonth - 1,0) + (1-1)),datetime)

--SELECT @StartDate,@EndDate
DECLARE @year INT
DECLARE @month INT
DECLARE @monthName VARCHAR(50)

CREATE TABLE #Results
(
MonthName VARCHAR(50)
,TASKNAME VARCHAR(500)
,ACTUALHOURS float
,EST_START_DATE DATETIME
,EST_END_DATE DATETIME
,ACT_START_DATE DATETIME
,ACT_END_DATE DATETIME
,PRJ_PROJECT_NAME VARCHAR(500)
,PRJ_CODE_ID VARCHAR(500)
,TASKNO VARCHAR(50)
,PRJ_SEQ_NO VARCHAR(50)
,CRQ_EFFORT_ON_IMPACT_ANALYSIS INT
,FLAG VARCHAR(1)
)

WHILE ((month(@StartDate) <= month(@EndDate)) AND (year(@StartDate) <= year(@EndDate)))
BEGIN

--SELECT @StartDate
--print @StartDate

SET @year = year(@StartDate)
SET @month = month(@StartDate)
SET @monthName = DATENAME(month, @StartDate)

INSERT INTO #Results
(
MonthName
,TASKNAME
,ACTUALHOURS
,EST_START_DATE
,EST_END_DATE
,ACT_START_DATE
,ACT_END_DATE
,PRJ_PROJECT_NAME
,PRJ_CODE_ID
,TASKNO
,PRJ_SEQ_NO
,FLAG
)
EXEC getQPMTask_bkp @month,@year,@monthName,@ProjectSeqNo,@ModuleIds

INSERT INTO #Results
(
MonthName
,TASKNAME
,ACTUALHOURS

,EST_START_DATE
,EST_END_DATE
,ACT_START_DATE
,ACT_END_DATE
,CRQ_EFFORT_ON_IMPACT_ANALYSIS
,PRJ_PROJECT_NAME
,PRJ_CODE_ID
,TASKNO
,PRJ_SEQ_NO
,FLAG
)
EXEC getQPMCR_bkp @month,@year,@monthName,@ProjectSeqNo,@ModuleIds

SET @StartDate = DATEADD(month,1,@StartDate)
--EXEC getQPMTask month(@StartDate),year(@StartDate)

END

error

Msg 517, Level 16, State 1, Procedure Rework, Line 23
Adding a value to a 'datetime' column caused overflow.
Msg 517, Level 16, State 1, Procedure Rework, Line 24
Adding a value to a 'datetime' column caused overflow.

(0 row(s) affected)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-05 : 05:10:12

i think error occurs in these two statements
SET @StartDate = cast(dateadd((mm,(@Fromyear-1900)* 12 + @Frommonth - 1,0) + (1-1)) ,datetime)
SET @EndDate = cast(dateadd((mm,(@Toyear-1900)* 12 + @Tomonth - 1,0) + (1-1)),datetime)

once check it
SET @StartDate = cast(dateadd(mm,(@Fromyear-1900)* 12 + @Frommonth - 1,0) + (1-1) as datetime)
SET @EndDate = cast(dateadd(mm,(@Toyear-1900)* 12 + @Tomonth - 1,0) + (1-1)as datetime)
CAST ( expression AS datatype )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 05:14:24

how did above code work for you? its not even syntactically correct.please post correct code if its different from this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:19:37

[code]SET @StartDate = dateadd(mm, (@Fromyear - 1900) * 12 + @Frommonth - 1, 0)
SET @EndDate = dateadd(mm, (@Toyear - 1900) * 12 + @Tomonth - 1, 0)
[/code]

E 12°55'05.63"
N 56°04'39.26"

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-05 : 07:40:22

hi

ya i changed that to cast as datetime now it is working fine
thank u
visakh the code is correct i have taken more than 1 sp in the main sp...so i cant send all the sp..thank u for replying

once again thank u

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-05 : 07:45:45

ur welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:58:51

quote:
Originally posted by shm

hi

ya i changed that to cast as datetime now it is working fine
thank u
visakh the code is correct i have taken more than 1 sp in the main sp...so i cant send all the sp..thank u for replying

once again thank u

nope...as bklr pointed out you were not using cast as ...thats what i told

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources