Ошибка арифметического переполнения при преобразовании expression к типу данных datetime - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Question
Hi All,

The following query is giving me this error —

Msg 8115, Level 16, State 2, Line 5

Arithmetic overflow error converting expression to data type datetime.

declare

@Year varchar(50),
@Month varchar(50)

set

@Year =
‘2010’

set

@Month =
’11’

select

*
from transaction_tnx

where

start_date_tnx = @Year+‘-‘+@Month+‘-‘+cast(
day(start_date_tnx)
as
nvarchar(20))

order

by start_date_tnx

For 2010 year and month 10 …only I have data….but for other years and months there is no data currently in the system….

I am not getting why its giving me the arithemetic over flow error..?

Any suggestions in this area…?

vineesh1701
- Moved by
  
  Tuesday, January 18, 2011 3:14 PM
  TSQL question (From:SQL Server Database Engine)

Answers

- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:58 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM
The format set by the SET DATEFORMAT instruction will apply even if you put the year in the first place; so you will get exactly the same error by using either 2010-10-31 than you will get with 10-31-2010. The following piece of code show you that the placement
of the year change nothing if you are using the separator «-» but that if you remove the separator «-«, the SET DATEFORMAT will lose its effect:
```
set dateformat dmy
select cast ('01-02-2010' as datetime), cast ('2010-01-02' as datetime),
cast ('20100102' as datetime)

set dateformat mdy
select cast ('01-02-2010' as datetime), cast ('2010-01-02' as datetime),
cast ('20100102' as datetime)
```
Sylvain Lafontaine, ing.
MVP — Access
Blog/web site:
http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)
- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:58 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM
The reason for the overflow error is that you try to build a date on the fly where the year part and month part is hardwired to November.

November has 30 days. But in your data, you have dates for other months of 31 days such as January, March, May, July, August, October and December.

So the on-the-fly build data is not a valid date.

Here is how you should do.
DECLARE @Year SMALLINT, @Month TINYINT SELECT @Year = 2011, @Month = 11 DECLARE @FromDate DATETIME, @ToDate = DATETIME SELECT @FromDate = DATEADD(MONTH, 12 * @Year - 22801 + @Month, 0), @ToDate = DATEADD(MONTH, 12 * @Year - 22800 + @Month, 0) SELECT * FROM dbo.Transaction_tnx WHERE start_date_tnx >= @StartDate AND start_date_tnx < @ToDate ORDER BY start_date_tnx
- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:59 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM

Источник

Remove From My Forums

Question
Hi All,

The following query is giving me this error —

Msg 8115, Level 16, State 2, Line 5

Arithmetic overflow error converting expression to data type datetime.

declare

@Year varchar(50),
@Month varchar(50)

set

@Year =
‘2010’

set

@Month =
’11’

select

*
from transaction_tnx

where

start_date_tnx = @Year+‘-‘+@Month+‘-‘+cast(
day(start_date_tnx)
as
nvarchar(20))

order

by start_date_tnx

For 2010 year and month 10 …only I have data….but for other years and months there is no data currently in the system….

I am not getting why its giving me the arithemetic over flow error..?

Any suggestions in this area…?

vineesh1701
- Moved by
  
  Tuesday, January 18, 2011 3:14 PM
  TSQL question (From:SQL Server Database Engine)

Answers

- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:58 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM
The format set by the SET DATEFORMAT instruction will apply even if you put the year in the first place; so you will get exactly the same error by using either 2010-10-31 than you will get with 10-31-2010. The following piece of code show you that the placement
of the year change nothing if you are using the separator «-» but that if you remove the separator «-«, the SET DATEFORMAT will lose its effect:
```
set dateformat dmy
select cast ('01-02-2010' as datetime), cast ('2010-01-02' as datetime),
cast ('20100102' as datetime)

set dateformat mdy
select cast ('01-02-2010' as datetime), cast ('2010-01-02' as datetime),
cast ('20100102' as datetime)
```
Sylvain Lafontaine, ing.
MVP — Access
Blog/web site:
http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)
- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:58 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM
The reason for the overflow error is that you try to build a date on the fly where the year part and month part is hardwired to November.

November has 30 days. But in your data, you have dates for other months of 31 days such as January, March, May, July, August, October and December.

So the on-the-fly build data is not a valid date.

Here is how you should do.
DECLARE @Year SMALLINT, @Month TINYINT SELECT @Year = 2011, @Month = 11 DECLARE @FromDate DATETIME, @ToDate = DATETIME SELECT @FromDate = DATEADD(MONTH, 12 * @Year - 22801 + @Month, 0), @ToDate = DATEADD(MONTH, 12 * @Year - 22800 + @Month, 0) SELECT * FROM dbo.Transaction_tnx WHERE start_date_tnx >= @StartDate AND start_date_tnx < @ToDate ORDER BY start_date_tnx
- Proposed as answer by
  Naomi N
  Wednesday, January 19, 2011 3:59 PM
- Marked as answer by
  KJian_
  Tuesday, January 25, 2011 8:36 AM

Источник

Remove From My Forums

Question

User-627428921 posted

I have a sql server 2000 database…using vb.net 2005… I have a form which allow user to find outstanding transaction by month selected in dropdownlist… If it matches the month it will populate out in datagrid, this date is store as dd/MM/yyyy…
below is the following code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If ddl_payment.SelectedItem.Text = "Outstanding" Then
                ddl_months.Visible = True
                datagrid1.Visible = False
                If ddl_months.Visible = True Then
                    config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE DATEPART(mm,dateOfPurchase)= '" + ddl_months.SelectedValue + "' AND balance > 0")
                    bindData()
                    datagrid1.Visible = False
                    datagrid.Visible = True
                End If
                
            ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then
                config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0")
                bindData1()
                datagrid.Visible = False
                datagrid1.Visible = True
                ddl_months.Visible = False
            Else
                datagrid.Visible = False
                datagrid1.Visible = False
                ddl_months.Visible = False
            End If

        End Sub

Below is the error:

Arithmetic overflow error converting expression to data type datetime.

Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.

Source Error:


Line 76:         cmd.CommandText = sqlStatement
Line 77:         cmd.Connection = conn
Line 78:         cmd.ExecuteNonQuery()
Line 79: 
Line 80:         reader = cmd.ExecuteReader

Source File: c:inetpubwwwrootTAKAApp_Codeconfig.vb Line:
78

Stack Trace:


[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
   TAKA.config.dataReader2(String sqlStatement) in c:inetpubwwwrootTAKAApp_Codeconfig.vb:78
   TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootTAKAPaymentStatus.aspx.vb:65
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I have another table which is staff table and it datefield is store in MM/dd/yyyy format

Answers

User-1827453801 posted

If u want to keep it as a string u’ll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.

But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text…?

If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not ’10’)

That’s interesting about the approval. I used to get that but stopped seeing it. I assumed they’d just made everything unmoderated but it’s probably just for user’s that havent reached ‘participant’ status.
- Marked as answer by
  
  Thursday, October 7, 2021 12:00 AM

User393453308 posted

Hi,

I think the here type of the field as being smalldatetime or datetime is not the issue.

Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute

Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.

So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.

I think shifting from datetime to smalldatetime will increase the performance.

If you want to know if some data would be truncated.

select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)

run this if you have any result than dont convert to smalldatetime.

 About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;

First be sure  ddl_months.SelectedValue is integer

then try the code as

 config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE DATEPART(m,dateOfPurchase) =  cast('"+ ddl_months.SelectedValue + "', as integer) AND balance > 0")

Hope this helps.

Marked as answer by
Anonymous
Thursday, October 7, 2021 12:00 AM

Источник

Remove From My Forums

Question

User-627428921 posted

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If ddl_payment.SelectedItem.Text = "Outstanding" Then
                ddl_months.Visible = True
                datagrid1.Visible = False
                If ddl_months.Visible = True Then
                    config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE DATEPART(mm,dateOfPurchase)= '" + ddl_months.SelectedValue + "' AND balance > 0")
                    bindData()
                    datagrid1.Visible = False
                    datagrid.Visible = True
                End If
                
            ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then
                config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0")
                bindData1()
                datagrid.Visible = False
                datagrid1.Visible = True
                ddl_months.Visible = False
            Else
                datagrid.Visible = False
                datagrid1.Visible = False
                ddl_months.Visible = False
            End If

        End Sub

Below is the error:

Arithmetic overflow error converting expression to data type datetime.

Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.

Source Error:





  


                       
  




Line 76:         cmd.CommandText = sqlStatement
Line 77:         cmd.Connection = conn
Line 78:         cmd.ExecuteNonQuery()
Line 79: 
Line 80:         reader = cmd.ExecuteReader

Source File: c:inetpubwwwrootTAKAApp_Codeconfig.vb Line:
78

Stack Trace:


[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
   TAKA.config.dataReader2(String sqlStatement) in c:inetpubwwwrootTAKAApp_Codeconfig.vb:78
   TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootTAKAPaymentStatus.aspx.vb:65
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I have another table which is staff table and it datefield is store in MM/dd/yyyy format

Answers

User-1827453801 posted

If u want to keep it as a string u’ll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.

But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text…?

If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not ’10’)

That’s interesting about the approval. I used to get that but stopped seeing it. I assumed they’d just made everything unmoderated but it’s probably just for user’s that havent reached ‘participant’ status.
- Marked as answer by
  
  Thursday, October 7, 2021 12:00 AM

User393453308 posted

Hi,

I think the here type of the field as being smalldatetime or datetime is not the issue.

Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute

Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.

So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.

I think shifting from datetime to smalldatetime will increase the performance.

If you want to know if some data would be truncated.

select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)

run this if you have any result than dont convert to smalldatetime.

 About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;

First be sure  ddl_months.SelectedValue is integer

then try the code as

 config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE DATEPART(m,dateOfPurchase) =  cast('"+ ddl_months.SelectedValue + "', as integer) AND balance > 0")

Hope this helps.

Marked as answer by
Anonymous
Thursday, October 7, 2021 12:00 AM

Источник

SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard Еще…Меньше

Проблемы

Рассмотрим следующий сценарий.

Вы создаете связанный сервер для Microsoft SQL Server 2012.
При попытке выполнить инструкцию SQL, вызывающую системную хранимую процедуру sys.sp_tables_info_90_rowset_64 , чтобы получить доступ к таблице из экземпляра SQL Server 2012 по умолчанию.
Таблица содержит более 2 500 000 000 записей.

В этом случае появляется следующее сообщение об ошибке:

Сообщение 8115, уровень 16, состояние 2, sp_tables_info_90_rowset_64 процедуры, строка 9Arithmetic ошибка переполнения при преобразовании выражения в тип данных int.

Примечание.Эта проблема возникает при настройке SQL Server 2012 в качестве целевого сервера.

Решение

Сведения о накопительном пакете обновления

Накопительный пакет обновления 1 (SP1) для SQL Server 2012 с пакетом обновления 1 (SP1)

Исправление для этой проблемы впервые выпущено в накопительном обновлении 1. За дополнительными сведениями о том, как получить этот накопительный пакет обновления для SQL Server 2012 с пакетом обновления 1 (SP1), щелкните следующий номер статьи базы знаний Майкрософт:

2765331 Накопительный пакет обновления 1 (SP1) для SQL Server 2012 с пакетом обновления 1 (SP1)Примечание. Так как сборки являются кумулятивными, каждый новый выпуск исправлений содержит все исправления и все исправления безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2012. Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:

2772858 Сборки SQL Server 2012, выпущенные после выпуска пакета обновления 1 (SP1) для SQL Server 2012

SQL Server 2012

Исправление для этой проблемы впервые выпущено в накопительном обновлении 4. Для получения дополнительных сведений о том, как получить этот накопительный пакет обновления для SQL Server 2012, щелкните следующий номер статьи базы знаний Майкрософт:

2758687 Накопительный пакет обновления 4 для SQL Server 2012Примечание. Так как сборки являются кумулятивными, каждый новый выпуск исправлений содержит все исправления и все исправления безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2012. Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:

2692828 Сборки SQL Server 2012, выпущенные после выпуска SQL Server 2012

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Дополнительная информация

Дополнительные сведения о том, как настроить связанные серверы в SQL Server 2012, можно найти на веб-сайте MSDN по следующему адресу:

Настройка связанных серверов в SQL Server 2012

Нужна дополнительная помощь?

Источник

Question

Answers

Question

Answers

Question

Arithmetic overflow error converting expression to data type datetime.

Answers

Question

Arithmetic overflow error converting expression to data type datetime.

Answers

Проблемы

Решение

Сведения о накопительном пакете обновления

Накопительный пакет обновления 1 (SP1) для SQL Server 2012 с пакетом обновления 1 (SP1)

SQL Server 2012

Статус

Дополнительная информация

Нужна дополнительная помощь?