Arithmetic overflow error converting expression to data type datetime

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 senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf 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 WHEREDATEPART(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 = sqlStatementLine 77: cmd.Connection = connLine 78: cmd.ExecuteNonQuery()Line 79: Line 80: reader = cmd.ExecuteReader

Source File:c:\inetpub\wwwroot\TAKA\App_Code\config.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:\inetpub\wwwroot\TAKA\App_Code\config.vb:78 TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\TAKA\PaymentStatus.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

DATEPART returns an integer so wrappingddl_months.SelectedValue in quotes is probably pointless.

Seeing as it's a sql exception I would say it's extremely likely that the field "dateOfPurchase" is of the wrong type. Perhaps ur storing it as a string or an int...?

Also sql doesn't store dates in a locale specific format, the fact that u've said "I have another table whihc is staff table and it datefield is store in MM/dd/yyyy format" suggests to me ur storing ur dates as strings which will not work.

|||

Furthermore executing unparameterised queries against sql is slap-worthy offence. Use parameters... or get hacked.

|||

I waited so long for it approved zzz...ya...worldspawn thx for reply... 4get to said that my date is in nvarchar format... erm so any idea how to get the month in the database in this format? Or do I need change it to smalldatetime format? I need smth like For example my database has this date(23/10/2007, 21/09/2007,22/10/2007)

How can i retrieve by month, If ddl_months i selected '10' den that 2 records with month 10 will shown in datagrid...

Thank in adv

|||

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.

|||

Would it be safe if i jus change my database dateOfPurchase to smalldatetime format? Cos i received prompt when trying to save... It say data may be lost when trying to convert nvarchar to smalldatetime... below is a few line example of my code... I select a date from calendar and display it in a textbox then save all the data enter in database... will there be any serious problem if i change?

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

todayD.Text = Calendar1.TodaysDate().ToString("dd/MM/yyyy")

End Sub

PrivateSub Calendar1_SelectionChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Calendar1.SelectionChanged

'display selected date from calendar

tb_dop.Text = Calendar1.SelectedDate().ToString("dd/MM/yyyy")

EndSub

PublicSub AddTransaction1(ByVal dateOfPurchaseAsString,ByVal totalAmountAsString,ByVal paymentReceivedAsString,ByVal bAsString,ByVal receiptInvoiceAsString,ByVal remarksAsString,ByVal csngInvoiceAsString,ByVal transDetailsAsString,ByVal coNameAsString)

'declare the INSERT method for INSERT transaction

Dim strSQLAsString ="INSERT INTO custTransaction (dateOfPurchase, totalAmount, paymentReceived, balance, receiptInvoice, remarks, csngInvoice, transDetails, coName) VALUES ('" + dateOfPurchase +"'," + totalAmount +"," + paymentReceived +"," + b +",'" + receiptInvoice +"','" + remarks +"', '" + csngInvoice +"', '" + transDetails +"', '" + coName +"')"

con1.Open()

Dim cmdAsNew SqlCommand(strSQL, con1)

cmd.ExecuteNonQuery()

con1.Close()

EndSub

PrivateSub btn_add2_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btn_add2.Click

dateOfPurchase = tb_dop.Text

If tb_csngInvoice.Text ="None"Then

'check for duplication

If tb_receiptInvoice.Text = config.dataReader2("SELECT receiptInvoice FROM custTransaction WHERE receiptInvoice = '" + tb_receiptInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

ElseIf tb_receiptInvoice.Text ="None"Then

'check for duplication

If tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

EndIf

EndSub

Thank you for ur time

|||

Is this production database? Honestly i have no idea what would happen exactly but i think it's safe to say ur existing date fields will become incorrect and it may encounter and error trying to convert them and not even work... make sure sql server's date format is dmy and it should work without any problems, if it's still set to mdy u'll have big problems.

Also if you want to display a date in the form dd/MM/yyyy use the inbuilt ToShortDateString method which will convert the date to that format (as long as ur server is configured to use that dateformat by default).

Please read a tutorial on how to property construct a sql command using parameters and not string concatenation. String concatentation leaves u open to sql injection attacks... plus it's just crappy.

|||

Hi, worldspawn can i know how u convert the sql server to display date in dd/MM/yyyy... although i have set the my own server to UK time but it still display as MM/dd/yyyy... Thanks

|||

I'm not a sql guru but I believe u type:

SET DATEFORMAT dmy

So just connect to the sql server with query analyzer or whatever and execute that. It's 6pm, i'm off home, best of luck :)

|||

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 WHEREDATEPART(m,dateOfPurchase) = cast('"+ ddl_months.SelectedValue + "', as integer)AND balance > 0")

Hope this helps.

|||

Hi yvzman,

A nice infomation to note... Thank for replying... But i think is use CONVERT instead of CAST... dunno y CAST is not longer supported by my script