Hi to all members!
I'm not familiar with SQL Server so I think to write in this forum to ask help to you, sql server gurus.
I try to write a procedure which return the error in the subject (Arithmetic overflow error converting expression to data type datetime) during a 'set' statement.
I don't understand what is the cause of the error because, in the procedure, there are similar statement that work correctly.
Down here there are the messages and the error returned when the procedure is executed.
"Init_D: Mar 12 2008 12:00AM
End_D: Mar 27 2008 12:00AM
End_D: Mar 31 2008 12:00AM
End_C: Mar 31 2008 12:00AM
ToPeriodo: Mar 31 2008 12:00AM
Periodo: Mar 10 2008 12:00AM-Mar 31 2008 12:00AM
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 101
Arithmetic overflow error converting expression to data type datetime.
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 116
Arithmetic overflow error converting expression to data type datetime.
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 131
Arithmetic overflow error converting expression to data type datetime.
FromPeriodo: Mar 31 2008 12:00AM
ToPeriodo: Apr 7 2008 12:00AM
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 153
A cursor with the name 'C_Backlog' does not exist.
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 154
A cursor with the name 'C_Aperti' does not exist.
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 155
A cursor with the name 'C_Chiusi' does not exist."
and this is the procedure......
"set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[Insert_Data_Rep] (@Sessione nvarchar(50), @InitPeriodo int, @EndPeriodo int) AS
Declare @Val int,
@InitPeriodo_Char nvarchar(24),
@EndPeriodo_Char nvarchar(24),
@InitPeriodo_Date datetime,
@EndPeriodo_Date datetime,
@DataRifer int,
@Periodo nvarchar(50),
@FromPeriodo datetime,
@ToPeriodo datetime,
@DayWeek int
Declare C_BackLog cursor for
Select 1, submit_date from pbm_problem_investigation
Where submit_date < @FromPeriodo
Declare C_Aperti cursor for
Select 1, submit_date from pbm_problem_investigation
Where submit_date >= @FromPeriodo
and submit_date < @ToPeriodo
Declare C_Chiusi cursor for
Select 1, last_modified_date from pbm_problem_investigation
Where investigation_status = 8
and last_modified_date >= @FromPeriodo
and last_modified_date < @ToPeriodo
Begin
set @InitPeriodo_Date = (select dbo.number2date(@InitPeriodo))
set @EndPeriodo_Date = (select dbo.number2date(@EndPeriodo))
print 'Init_D: ' + convert(varchar(30), @InitPeriodo_Date)
print 'End_D: ' + convert(varchar(30), @EndPeriodo_Date)
-- Dalla data iniziale ricavo il lunedì precedente
set @DayWeek = (select datepart (dw, @InitPeriodo_Date))
If @DayWeek >= 2
set @FromPeriodo = (select dateadd(dd, 2-@DayWeek, @InitPeriodo_Date))
else
set @FromPeriodo = (select dateadd(dd, @DayWeek-7, @InitPeriodo_Date))
set @InitPeriodo_Date = @FromPeriodo
set @InitPeriodo_Char = convert(nvarchar(24), @FromPeriodo)
--------------------------
--print 'Init_D: ' + convert(varchar(30), @InitPeriodo_Date)
--print 'Init_C: ' + @InitPeriodo_Char
-- Dalla data finale ricavo il lunedì successivo
set @DayWeek = (select datepart (dw, @EndPeriodo_Date))
If @DayWeek > 2
set @ToPeriodo = (select dateadd(dd, 7-(@DayWeek-2), @EndPeriodo_Date))
else
set @ToPeriodo = (select dateadd(dd, 2-@DayWeek, @EndPeriodo_Date))
--print 'ToPeriodo: ' + convert(varchar(24), @ToPeriodo)
--set @EndPeriodo_Date = cast(convert(datetime, @ToPeriodo, 109) as datetime)
set @EndPeriodo_Char = convert(nvarchar(24), @ToPeriodo)
set @EndPeriodo_Date = cast(@ToPeriodo as datetime)
--------------------------
print 'End_D: ' + convert(varchar(30), @EndPeriodo_Date)
print 'End_C: ' + @EndPeriodo_Char
--set @ToPeriodo = (select dateadd(day, 7, @FromPeriodo))
set @Periodo = @InitPeriodo_Char+'-'+@EndPeriodo_Char
print 'ToPeriodo: ' + convert(varchar(24), @ToPeriodo)
print 'Periodo: ' + @Periodo
/*
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Backlog: ', 0, @Sessione)
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Aperti: ', 0, @Sessione)
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Chiusi: ', 0, @Sessione)
*/
While @ToPeriodo <= @EndPeriodo_Date
Begin
Open C_Backlog
Fetch Next From C_Backlog Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Backlog: ', @Val, @Sessione)
Fetch Next From C_Backlog Into @Val, @DataRifer
End
Close C_Backlog
Deallocate C_Backlog
Open C_Aperti
Fetch Next From C_Aperti Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Aperti: ', @Val, @Sessione)
Fetch Next From C_Aperti Into @Val, @DataRifer
End
Close C_Aperti
Deallocate C_Aperti
Open C_Chiusi
Fetch Next From C_Chiusi Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Aperti: ', @Val, @Sessione)
Fetch Next From C_Chiusi Into @Val, @DataRifer
End
Close C_Chiusi
Deallocate C_Chiusi
set @FromPeriodo = @ToPeriodo
set @ToPeriodo = (select dateadd(dd, 7, @ToPeriodo))
print 'FromPeriodo: ' + convert(varchar(30), @FromPeriodo)
print 'ToPeriodo: ' + convert(varchar(30), @ToPeriodo)
End
Deallocate C_Backlog
Deallocate C_Aperti
Deallocate C_Chiusi
End"
The statement "set @EndPeriodo_Date = cast(@ToPeriodo as datetime)" probably seems to be incorrect cause the variable @ToPeriodo is just a datetime so the conversion wasn't necessary. I try to do this, cause also the original statement (set @EndPeriodo_Date = @ToPeriodo ) returned the same error.
Contact me for other info.
Thanks to all in advance.
Leo.
I'm not familiar with SQL Server so I think to write in this forum to ask help to you, sql server gurus.
I try to write a procedure which return the error in the subject (Arithmetic overflow error converting expression to data type datetime) during a 'set' statement.
I don't understand what is the cause of the error because, in the procedure, there are similar statement that work correctly.
Down here there are the messages and the error returned when the procedure is executed.
"Init_D: Mar 12 2008 12:00AM
End_D: Mar 27 2008 12:00AM
End_D: Mar 31 2008 12:00AM
End_C: Mar 31 2008 12:00AM
ToPeriodo: Mar 31 2008 12:00AM
Periodo: Mar 10 2008 12:00AM-Mar 31 2008 12:00AM
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 101
Arithmetic overflow error converting expression to data type datetime.
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 116
Arithmetic overflow error converting expression to data type datetime.
Msg 8115, Level 16, State 2, Procedure Insert_Data_Rep, Line 131
Arithmetic overflow error converting expression to data type datetime.
FromPeriodo: Mar 31 2008 12:00AM
ToPeriodo: Apr 7 2008 12:00AM
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 153
A cursor with the name 'C_Backlog' does not exist.
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 154
A cursor with the name 'C_Aperti' does not exist.
Msg 16916, Level 16, State 1, Procedure Insert_Data_Rep, Line 155
A cursor with the name 'C_Chiusi' does not exist."
and this is the procedure......
"set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[Insert_Data_Rep] (@Sessione nvarchar(50), @InitPeriodo int, @EndPeriodo int) AS
Declare @Val int,
@InitPeriodo_Char nvarchar(24),
@EndPeriodo_Char nvarchar(24),
@InitPeriodo_Date datetime,
@EndPeriodo_Date datetime,
@DataRifer int,
@Periodo nvarchar(50),
@FromPeriodo datetime,
@ToPeriodo datetime,
@DayWeek int
Declare C_BackLog cursor for
Select 1, submit_date from pbm_problem_investigation
Where submit_date < @FromPeriodo
Declare C_Aperti cursor for
Select 1, submit_date from pbm_problem_investigation
Where submit_date >= @FromPeriodo
and submit_date < @ToPeriodo
Declare C_Chiusi cursor for
Select 1, last_modified_date from pbm_problem_investigation
Where investigation_status = 8
and last_modified_date >= @FromPeriodo
and last_modified_date < @ToPeriodo
Begin
set @InitPeriodo_Date = (select dbo.number2date(@InitPeriodo))
set @EndPeriodo_Date = (select dbo.number2date(@EndPeriodo))
print 'Init_D: ' + convert(varchar(30), @InitPeriodo_Date)
print 'End_D: ' + convert(varchar(30), @EndPeriodo_Date)
-- Dalla data iniziale ricavo il lunedì precedente
set @DayWeek = (select datepart (dw, @InitPeriodo_Date))
If @DayWeek >= 2
set @FromPeriodo = (select dateadd(dd, 2-@DayWeek, @InitPeriodo_Date))
else
set @FromPeriodo = (select dateadd(dd, @DayWeek-7, @InitPeriodo_Date))
set @InitPeriodo_Date = @FromPeriodo
set @InitPeriodo_Char = convert(nvarchar(24), @FromPeriodo)
--------------------------
--print 'Init_D: ' + convert(varchar(30), @InitPeriodo_Date)
--print 'Init_C: ' + @InitPeriodo_Char
-- Dalla data finale ricavo il lunedì successivo
set @DayWeek = (select datepart (dw, @EndPeriodo_Date))
If @DayWeek > 2
set @ToPeriodo = (select dateadd(dd, 7-(@DayWeek-2), @EndPeriodo_Date))
else
set @ToPeriodo = (select dateadd(dd, 2-@DayWeek, @EndPeriodo_Date))
--print 'ToPeriodo: ' + convert(varchar(24), @ToPeriodo)
--set @EndPeriodo_Date = cast(convert(datetime, @ToPeriodo, 109) as datetime)
set @EndPeriodo_Char = convert(nvarchar(24), @ToPeriodo)
set @EndPeriodo_Date = cast(@ToPeriodo as datetime)
--------------------------
print 'End_D: ' + convert(varchar(30), @EndPeriodo_Date)
print 'End_C: ' + @EndPeriodo_Char
--set @ToPeriodo = (select dateadd(day, 7, @FromPeriodo))
set @Periodo = @InitPeriodo_Char+'-'+@EndPeriodo_Char
print 'ToPeriodo: ' + convert(varchar(24), @ToPeriodo)
print 'Periodo: ' + @Periodo
/*
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Backlog: ', 0, @Sessione)
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Aperti: ', 0, @Sessione)
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @InitPeriodo, 'Chiusi: ', 0, @Sessione)
*/
While @ToPeriodo <= @EndPeriodo_Date
Begin
Open C_Backlog
Fetch Next From C_Backlog Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Backlog: ', @Val, @Sessione)
Fetch Next From C_Backlog Into @Val, @DataRifer
End
Close C_Backlog
Deallocate C_Backlog
Open C_Aperti
Fetch Next From C_Aperti Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Aperti: ', @Val, @Sessione)
Fetch Next From C_Aperti Into @Val, @DataRifer
End
Close C_Aperti
Deallocate C_Aperti
Open C_Chiusi
Fetch Next From C_Chiusi Into @Val, @DataRifer
While @@FETCH_STATUS = 0
Begin
Insert Into BNL_Rep_Trend_Periodo
(Periodo, DataRif, TipoDato, ValoreDato, Session)
Values
(@Periodo, @DataRifer, 'Aperti: ', @Val, @Sessione)
Fetch Next From C_Chiusi Into @Val, @DataRifer
End
Close C_Chiusi
Deallocate C_Chiusi
set @FromPeriodo = @ToPeriodo
set @ToPeriodo = (select dateadd(dd, 7, @ToPeriodo))
print 'FromPeriodo: ' + convert(varchar(30), @FromPeriodo)
print 'ToPeriodo: ' + convert(varchar(30), @ToPeriodo)
End
Deallocate C_Backlog
Deallocate C_Aperti
Deallocate C_Chiusi
End"
The statement "set @EndPeriodo_Date = cast(@ToPeriodo as datetime)" probably seems to be incorrect cause the variable @ToPeriodo is just a datetime so the conversion wasn't necessary. I try to do this, cause also the original statement (set @EndPeriodo_Date = @ToPeriodo ) returned the same error.
Contact me for other info.
Thanks to all in advance.
Leo.