Array in expression editor anyone?

I'm kind of a newbie at all this and I'm wondering if I can do the
following. Whithout having to use custom code is there a way to use the IN
function in the expression editor?
I'm currently using the following to accomplish something quite simple:
=IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
month(Fields!por_POSTING_DATE.Value) = 11 or
month(Fields!por_POSTING_DATE.Value) = 12), True,False)
I'd like to use:
=IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
Can you not use an array in the expression editor (and only in custom code?)
Thanks,
RyanHere's one way to do it:
=iif(Array.BinarySearch(new Integer() {10,11,12},
month(Fields!por_POSTING_DATE.Value))>=0,True,False)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:evyaKVzeEHA.2352@.TK2MSFTNGP09.phx.gbl...
> I'm kind of a newbie at all this and I'm wondering if I can do the
> following. Whithout having to use custom code is there a way to use the
IN
> function in the expression editor?
> I'm currently using the following to accomplish something quite simple:
> =IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
> month(Fields!por_POSTING_DATE.Value) = 11 or
> month(Fields!por_POSTING_DATE.Value) = 12), True,False)
> I'd like to use:
> =IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
> Can you not use an array in the expression editor (and only in custom
code?)
> Thanks,
> Ryan
>|||Thanks Chris that worked great.
Ryan
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:%23OSa8%23AfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Here's one way to do it:
> =iif(Array.BinarySearch(new Integer() {10,11,12},
> month(Fields!por_POSTING_DATE.Value))>=0,True,False)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:evyaKVzeEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > I'm kind of a newbie at all this and I'm wondering if I can do the
> > following. Whithout having to use custom code is there a way to use the
> IN
> > function in the expression editor?
> > I'm currently using the following to accomplish something quite simple:
> >
> > =IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
> > month(Fields!por_POSTING_DATE.Value) = 11 or
> > month(Fields!por_POSTING_DATE.Value) = 12), True,False)
> > I'd like to use:
> > =IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
> >
> > Can you not use an array in the expression editor (and only in custom
> code?)
> >
> > Thanks,
> >
> > Ryan
> >
> >
>

Arithmetic overflow error converting expression to data type int.

I've got this error message while generate the output with ASP:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."

it indicate that the error is related to this line:
"rc1.Movenext"

where rc1 is set as objconn.Execute(sql).

Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.

Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.could you perhaps show the query that caused the error?|||Here is the query:

CREATE procedure rp_co_relatedcompanies @.companyregistrationno varchar(20)
as
set nocount on
--used for related companies details

select a.companypersonid,a.personname,a.personaddress1,a. personaddress2,a.personcity,a.personstate,a.person country,
a.personzip,a.personphone,a.personfax,a.personemai l,a.personuniqueid,b.principalbusiness,b.secondryb usiness,
c.relationwithcompany,c.numberofshares, c.classofsharesheld , cast(d.noofissuedshares as int) 'noofissuedshares',
'percentageofsharehold' = case when c.numberofshares =0 then 0 else c.numberofshares/d.noofissuedshares*100 end, c.otherremarks
from companyperson a
left join companystructure d on a.personuniqueid=d.companyregistrationno
left outer join operationsandactivity b on a.personuniqueid = b.companyregistrationno
left outer join relationshipcompanyperson c on a.companypersonid = c.companypersonid
where c.companyregistrationno = @.companyregistrationno and c.relationshipwith = 2
and a.deletionflag=0 order by a.personname
set nocount off
GO|||can you see anything in that query where there might be a problem "converting expression to data type int"

the immediate suspect is cast(d.noofissuedshares as int)

another suspect is c.numberofshares/d.noofissuedshares*100

check those and let us know|||Table DDL would help a lot. Heck, I was betting on the c.companyregistrationno being an INT and the @.companyregistrationno failing the implicit Cast...

Anybody want to start a pool on this? Or another option (not as much fun, but the problem would get solved a lot faster) would be for yllas to post the DDL for all of the tables in this query!

-PatP

Arithmetic overflow error converting expression to data type int.

Hello, I'm using SQL Server 7.0 (SP2) for my distributor and many subscribers. It seems that when I replicate using my subscribers, some of them hit the error "Arithmetic overflow error converting expression to data type int". However, I realised that after waiting for a while before replicating now, it seems okay. Anybody can advice how I can prevent this from happening? Thanks!Refer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307533&gssnb=1) to fix the issue.

Arithmetic overflow error converting expression to data type int.

select convert(bigint,21568194 * 100)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Why does this happen, How do I fix?select convert(bigint,CAST(21568194 AS bigint) * 100)
OR
select CAST(21568194 AS bigint) * 100
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:BB3435B9-09B1-4260-B03B-5F19E78E084F@.microsoft.com...
> select convert(bigint,21568194 * 100)
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> Why does this happen, How do I fix?

Arithmetic overflow error converting expression to data type int.

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

Quote:

Originally Posted by Hulikal

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

maybe you should consider adding year instead of seconds on your date...that's a lot of seconds|||I would take the number of seconds, divide it out;

There are 31536000 Seconds in a year so store that value in a variable as 1 or 2, ect depending upon how many years you have.

Subtract 31536000 * (the value stored from above) from your starting value to get the remaining time. Then do the same for days, then minutes, then seconds.

Do not do this for months as each month has varying number of days.

This way you will date add to Year, Days, Minutes, Seconds.

This will be more manageable.

Arithmetic overflow error converting expression to data type int when using Fill from a Sq

I thought I'd post this quick problem and answer, as I couldn't find the answer when searching for it.

I tried to call a stored procedure on SQL Server 2000 using the System.Data.SqlClient objects, and was not expecting any unusual issues. However when I came to call the Fill method I received the error "Arithmetic overflow error converting expression to data type int."

My first checks were the obvious ones of verifying that I'd provided all the correct datatypes and had no unexpected null values, but I found nothing out of order. The problem turns out to be a difference on the maximum values for integers between C# and SQL Server 2000. Previously having hit issues with SQL Server integers requiring Long Integer types in VB6, I was aware that these are 32-bit integers, so I was passing in Int32 variables. The problem was that Int32.MaxValue is not a valid integer for SQL Server. Seeing as I was providing an abitrary upper value for records-per-page (to fetch all in one page), I was simply able to change this to Int16.MaxValue and will hit no further problems as this is also well beyond any expected range for this parameter.

If anyone can name off the top of their heads what value should be provided as a maximum integer for SQL Server 2000, this might be a useful addition, but otherwise I hope this spares others some hunting if they also experience this problem.

James Burton

Hi,

Please check in the database to see if there is any calculation to be done in your DataSet.

Sometimes, there might be expression columns that add value to this column which might make the exception.

Arithmetic overflow error converting expression to data type int

Under certain circumstances I am getting the following error

"Arithmetic overflow error converting expression to data type int"

when running the following code:

SELECT Count(*), Sum(GrossWinAmount)
FROM LGSLog
WHERE
(CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')

If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.My first suggestion would be:SELECT Count(*), Sum(Cast(GrossWinAmount AS MONEY))
FROM LGSLog
WHERE CurrentDate >= '9/1/2004 8:00:00 AM'
And CurrentDate <= '9/27/2004 7:59:59 AM'-PatP|||I don't think that would accomplish anything in this case since floating point and monitary values such as this are stored in the database, for the most part, as an SQL int type (multiplied by 100 and rounded in the program(s) before being written... don't ask why... it was being done this way before I started working on the system).|||Did you try it, or are you just guessing that it won't work?

-PatP|||Yep. I just tried changing the query as follows (I changed the date range to only include a week's worth of data... in the program, these are actually datetime variables)...
:
declare @.Totals TABLE
(
GameCount int default 0,
Win int default 0,
Adj int default 0,
Bet int default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS MONEY)) As Win, Sum(CAST(l.AdjustedWinAmount AS MONEY)) As Adj, Sum(CAST(l.TotalBetAmount AS MONEY)) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/20/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin /* make sure something valid is returned if nothing found */
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals
:
and got the following error...
:
There is insufficient result space to convert a money value to int.
The statement has been terminated.
:
Which suggests to me that Sum is still wanting to RETURN an int size value, which is what I thought. The theory is that it is not the size of what is being passed into Sum(), but the fact that Sum() is trying to return a value that is too big because it wants to return a value the size of an int!|||How about the DDL for LGSLog

And how many rows are in the result set?

What are the MIN and MAX Values for those columns?

What is the average wind-speed velocity of a sparrow?|||Could it be that the result went outsideof the alloweable boundaries (922337203685477.5807 or -922337203685477.5808)?|||I think I finally got it to work. It took a hybrid of your original suggestion. I cast the sum results to money values / 100 (floats gave me too imprecise a number). I also changed the result set data fields to money values. Then, in the program, I multiplied by 100 and rounded and then converted to long integer (the program already expcected the values as a fixed point type stored in a long... fixed 2 places left of the right most digit... it was easier this way than to make major mods throughout program). I just hope this does not introduce rounding errors, which was the main reason for storing as fixed point values converted to integers in the first place.

This technique seemed to work getting data as far back as one month (no overflow/conversion errors).

declare @.Totals TABLE
(
GameCount int default 0,
Win money default 0,
Adj money default 0,
Bet money default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS money) / 100) As Win,
Sum(CAST(l.AdjustedWinAmount AS money) / 100) As Adj, Sum(CAST(l.TotalBetAmount AS money) / 100) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/01/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals

The grouping broke the results into anywhere from a couple of rows to over 25, depending on how far back I wanted to go. This also helped to reduce the size of the value being summed at the server. If the number got too big in the program, I could just use a larger integer size, such as an int32 or int64 in the program, which is not an option I had at the server.|||Instead of casting to MONEY and doing the divide by 100 then multiplying the sum by 100, just cast the column to BIGINT which is an int64 equivalent.

I'm still having trouble getting my head around the idea that you overflowed a MONEY... MONEY is big enough to express the US national debt in Argentine Pesos!

-PatP|||I don't think it actually "OVERFLOWED" the money type. I think it had no problem working with money. I think what was happening is that when "Sum" RETURNED the sum value, SQL tried to convert the "money" value to an "int" type, overflowing the "int" type during the conversion. This is why I think that the fields in the result set had to go from the integerial type to a money. I think I tried converting the value being passed into Sum, and also tried storing the result into a bigint, but never both at the same time. It always resulted in a conversion to an integer at some point... going in or coming out of Sum.

Apparently, if you pass an int type into Sum, it is going to return an int, which is what I was doing originally. If you pass in a money, it will sum internally as a money type and return a money type. But if the receiver variable is an int, it will downcast to an int, overflowing the resulting value. If you store the result into a money type, then it is going straight from a money to a money... no downcast necessary.|||For what it is worth, MONEY actually stores as an 8 byte signed integer, with implicit scaling by 1e4 (basically four digits after the decimal place). If your application is dealing with ODBC data directly like a C++ program would, you'd see the result come back as a long int that needed to be divided by 10000 (or in your case only 100).

-PatP

Arithmetic overflow error converting expression to data type datetime.

Ya ? am taking this error message in Asp.Net and i am not inserting a new row my database.

i have a databese and my fields

EmailID-->int

EmailAdress-->varchar(100)

DateTime-->datetime

IPAdress-->char(15)

Please help me,thanks

Most likely a disparity between the application date format and the date format SQL Server 'expects'. (Application passing the date parameter in the form of dd/mm/yyyy, and SQL Server accepting that as mm/dd/yyyy -and the ensuing date is invalid.

Have the application pass the date parameter in the ISO form of: yyyy/mm/dd OR yyyymmdd.

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

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Arithmetic overflow error converting expression to data type bigint

I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:

USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,

vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO

When the wizard gets to the step where it is creating the publication, I get the following error message:

Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).

I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.

Thanks in advance ...

David L. Collison

Any day above ground is a good day.

We need more information - are you doing any filtering or joining of any articles that may cause this error?

You can also do a profile trace of the publisher when you click on the OK button to complete the wizard so you can see what stored proc and statement it's failing on. Let us know what you find.

|||Greg ...

This has to be due to the size of my key on the file. I resized the field ID to NUMERIC(12,0) and now the replication wizard completes the setup properly.

I would have anticipated the replication engine to utilize the definition - obviously it doesn't like big numbers. Ok, I know it will take a long time to create that many records to worry about filling up the key at 12 digits let alone 19, but I like to plan ahead. ;-)

Have a good one!

David L. Collison
Any day above ground is a good day!

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regards
datediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards
|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regardsdatediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx
.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow

Hi,
Every time a run a query where dates are included, I get an 'Arithmetic
overflow error converting expression to data type datetime'.
The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
AS Month' syntax is used.
I think that somehow, some of the dates are corrupted.
How can I found out which of then are corrupted? There're too many to scroll
through.
TIA,
Ana
************************************************** *****************************
"Eres dueo de lo que callas y esclavo de lo que dices"
"Judge your success by what you had to give up in order to get it"
************************************************** *****************************
On Tue, 28 Sep 2004 23:22:39 +0200, Ana wrote:

>The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
>AS Month' syntax is used.
>I think that somehow, some of the dates are corrupted.
>How can I found out which of then are corrupted?
Hi Ana,
SELECT YourDateCol
FROM YourTable
WHERE ISDATE(YourDateCol) = 0
And change your database design: use the datetime format to store dates
instead. <g>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Argument Not Specified For parameter 'TruePart' of Public Function

why am I getting the error "Argument Not Specified For parameter 'TruePart'
of 'Public Function IIf(Expression as Boolean, TruePart as Object, FalsePart
as Object) as Object' from the following textbox expression in reporting
services 2005?
=IIF(Sum(Fields!ElapsedRunSeconds.Value)is Nothing, 0,
Sum(Fields!ElapsedRunSeconds.Value)/60.0/60.0)Stacey,
Try =IIF(Sum(Fields!ElapsedRunSeconds.Value) is Nothing, 0, Sum(Fields!
ElapsedRunSeconds.Value)/3600.0)
Inserting a space between "Sum(Fields!ElapsedRunSeconds.Value)" and
"is" or changing is to = should fix it.
Changing the /60.0/60.0 to /3600.0 is mostly for looks and
simplicity. HTH
toolman
stacey wrote:
> why am I getting the error "Argument Not Specified For parameter 'TruePart'
> of 'Public Function IIf(Expression as Boolean, TruePart as Object, FalsePart
> as Object) as Object' from the following textbox expression in reporting
> services 2005?
> =IIF(Sum(Fields!ElapsedRunSeconds.Value)is Nothing, 0,
> Sum(Fields!ElapsedRunSeconds.Value)/60.0/60.0)