Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Runtime error "Msg 248" overflowed an int column

Status
Not open for further replies.

rjsql

MIS
Joined
Apr 2, 2003
Messages
5
Location
US
CREATE PROCEDURE GENDSTDATEEX
(@p_Time_Zone VARCHAR(100),
@p_TZ_Start_Date INT,
@p_TZ_End_Date INT,
@p_TZ_Start_Time INT,
@p_TZ_End_Time INT,
@po_Error_Code INT OUTPUT,
@po_Error_Msg VARCHAR(500) OUTPUT
)
AS

BEGIN
DECLARE

@DecimalVar INT,
@v_Rowcount INT,
@v_Time_Zone VARCHAR(100),
@v_TZ_Start_Date INT,
@v_TZ_End_Date INT,
@v_TZ_Start_Time INT,
@v_TZ_End_Time INT,
@e_Error_Code INT,
@e_Error_Msg VARCHAR(100),
@v_year INT,
@v_cnt INT,
@c_Total_Date_Sec INT,
@v_Start_Year INT,
@v_End_Year INT,

@v_UTC_Offset INT,
@v_Daylight_Savings_Offset INT,
@v_Local_DST_Start_Date INT,
@v_Local_DST_Start_Time INT,
@v_Local_DST_End_Date INT,
@v_Local_DST_End_Time INT,
@v_DST_Start BIGINT,
@v_DST_End BIGINT

SET @v_cnt = 1
SET @DecimalVar = -1200.02
SET @c_Total_Date_Sec = 86400
SET @v_TZ_Start_Date = 0
SET @v_TZ_End_Date = 0
SET @v_year = 0

BEGIN
SELECT
@v_UTC_Offset = GMT_OFFSET, @v_Daylight_Savings_Offset = DAYLIGHT_SAVINGS_OFFSET , @v_DST_Start = DST_START,
@v_DST_End = DST_END
FROM
Time_Zone
WHERE
DESCRIPTION = @p_Time_Zone



IF @po_Error_Code > 0 GOTO EXCEPTION


SET @v_Start_Year = CONVERT(INT,SUBSTRING(CONVERT(CHAR,@p_TZ_Start_Date),1,4))
SET @v_End_Year = CONVERT(INT,SUBSTRING(CONVERT(CHAR,@p_TZ_End_Date),1,4))

SET @v_year = @v_Start_Year

/* Create global temporary table to hold the start and end dates for each year of the reporting interval */

CREATE TABLE #DST_DATE_TBLE (START_DATE INT, END_DATE INT, START_TIME INT, END_TIME INT, IS_DST INT)

/* Store into table converted set of specific start and end dates after taking into account Daylight Savings Time */

WHILE ( @v_year <= @v_End_Year )
BEGIN

EXEC Convert_DST @v_year, @v_DST_Start, @v_Local_DST_Start_Date, @v_Local_DST_Start_Time, @po_Error_Code OUTPUT, @po_Error_Msg OUTPUT
IF @po_Error_Code > 0
BEGIN
SET @E_ERROR_CODE = @po_Error_Code
SET @E_ERROR_MSG = @po_Error_Msg
GOTO EXCEPTION
END

EXEC Convert_DST @v_year, @v_DST_End, @v_Local_DST_End_Date, @v_Local_DST_End_Time, @po_Error_Code OUTPUT, @po_Error_Msg OUTPUT
IF @po_Error_Code > 0
BEGIN
SET @E_ERROR_CODE = @po_Error_Code
SET @E_ERROR_MSG = @po_Error_Msg
GOTO EXCEPTION
END

IF( @v_TZ_Start_Date <= @v_Local_DST_Start_Date and @v_Local_DST_Start_Date <= @v_TZ_End_Date) AND
( @v_TZ_Start_Date <= @v_Local_DST_End_Date and @v_Local_DST_End_Date <= @v_TZ_End_Date)
BEGIN
INSERT INTO #DST_DATE_TBLE VALUES (@v_Local_DST_Start_Date, @v_Local_DST_End_Date, 0, 0, 0)
END

SET @v_year = @v_year + 1

END

END

EXCEPTION:
BEGIN
SET @po_Error_Code = 7
SET @po_Error_Msg = 'Time Zone Input Error!! - Undefine Time Zone.'
END


END
GO

The runtime error I get is
&quot;Server: Msg 248, Level 16, State 1, Procedure GENDSTDATEEX, Line 47
The conversion of the varchar value '5110020000' overflowed an int column. Maximum integer value exceeded.&quot;

Line 47 is the SELECT clause in red above.

My exec input is the following :
Declare @error_code_output INT
Declare @error_msg_output varchar(4000)
EXEC GENDSTDATEEX '(GMT-08:00) Pacific Time (US & Canada); Tijuana', '20021122',
'20021127',000000,235959,@po_error_code = @error_code_output OUTPUT,
@po_error_msg = @error_msg_output OUTPUT[/]
What is causing this error? Thanks in advance!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top