Dear Programmers,
I want to store a number of .NET type double (16,95) into the database
column of type numeric, but it is stored as integer (17) and when I call the
value in my application, it is displayed as 17. How can I solve this
problem? Thanks in advance,
Burak
Hi all,
I am sorry if i am posting this error in an inappropriate froum.
Well in my asp.net intranet web application i want to enter a number to the database(sql sever 2005) that has a column(schoolkm) whose type is decimal(9, 2). Now if i want to enter the value 1.5 in the text box and enter that value to the database through interface then that value automatically rounds to 2. But when i get into the table and enter that value by hand then that value enters perfectly i.e. without rounding of. I want to know the reason and how can i cure this problem.
Regards & thanks in advance
I have table with data type decimal (18,2) when i try to load more then 2 decimal it is rounding off
Example 34.456 is rounded with 34.46
I want to store 34.45 only with out round in decimal data type how can i achive this
Hello,
I have a problem with the round of SQL Server 2K on the float data type.
In my application I do a SQL request for find a row in a table of SQL Server 2K.
This is the request :
SELECT DISTINCT N_ROW_ID FROM COMM WHERE N_ARTICLE_ID=79510 AND N_DATASOURCE_ID=1 AND N_SOURCE_ID=-102 AND N_PROVIDER_ID=-100
AND N_LEAD_TIME IS NULL AND N_UNIT_PRICE = 329.78 AND N_UNIT_PRICE_CURRENCY_ID=1
N_UNIT_PRICE is a float data type.
In the line need, the field N_UNIT_PRICE has 329.78 but with the round of SQL Server 2K I have 329.779999999999997.
So I never find the row
Do you have a solution for comparate a float flied with SQL Server round and a float send in a request as my 329.78?
I can't transform the column in decimal or numeric data type.
Is it possible to Disable the function?
Regards
How do I write sql syntax to round the last digit. For example, 12.152 should be 12.15. My current sql syntax does not round the last digit as follows:
Select tblARInvoiceDetail.UnitPrice * tblARInvoiceDetail.Quantity AS ChargeBeforeDiscount
FROM tblARInvoices INNER JOIN
tblARInvoiceDetail ON
tblARInvoices.ARInvoiceID = tblARInvoiceDetail.ARInvoiceID
The tblARInvoiceDetail.UnitPrice column is numeric;length 9;precision 18
The Quantity column is integer;length 4; precision 10;scale 0
Any Suggestions?
Thanks,
Denise
T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks
View 1 Replies View RelatedHow can I round up a decimal number? in SSRS reports.
View 7 Replies View RelatedHi,
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);
this.mycourtsDataSet1.AcceptChanges();
this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);
This did not happen with VS2005/CE3.01.
I have tried changing all references to decimal (or money in CE3.5) without luck.
I'm beginning to think that string may be the way to go!!!!!!!
Can someone shed some light on my problem.
Thanks,
Later:
It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
What data type in SQL server can be used in place of a double data type?? I don't even know what a double data type is but got a request to create a column with a data type of double.
Thanks.
Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ....Code:Public Sub UpdateCallStatus() Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String CALLID = Request.QueryString("CallID") RequestorID = Session("USER_ID") CommentsFromITD = lblcomments.Text MessageFromITD = lblmessage.Text MessageToITD = txt_desc.Text CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID))
Dim ObjCmd As SqlCommand Dim ObjDR As SqlDataReader
Try If CallStatus = 10 Then strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed") Response.Redirect("UserCallClosed.aspx") ObjConn.Close() Else strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close()
strSQL = "SELECT STATUS_LABEL FROM STATUS WHERE STATUS_ID = " & CallStatus & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close()
gbVariables.insertuserevents(CALLID, RequestorID, CallStatus) CallStatus = "" End If Catch ex As Exception lblmsg.Text = ex.Message.ToString End Try End SubThanks...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
Hi all
if i run
SELECT ROUND(700/1224) -- from sql server queyr analysier iam getting value as "0"
but same query i run in Oracle
SELECT ROUND(700/1224) FROM DUAL; iam getting value ".571895425"
what is reason can some body explan me
Greetings:
Is there a way of rounding up floating point numbers without CASTing as shown in a number of the replies ? For example, a table value is 103.365 and I'd like to see values of 103.365 and above (103.366,103.367, 103.368, 103.369) rounded to 103.37. Is is possible to do this with a function or setting in SQL Server 2005 or is it necessary to write T-SQL to do the rounding ?
Thanks.
alan
Hi there!
I'm using a Switch statement in my SQL as follows:
Code:
SELECT symbol,
Switch(timestamp Is Null,Null,
timestamp <= 005959, 0,
timestamp<=235959,23) AS period
INTO averageprice
FROM stocktrades;
Now here's my problem. The Data Type stored in the 'period' field of this new table I've created, dubbed averageprice, is a Text field and I want it to be a Number field. I've tried my best to figure this out and I'm still looking so any helpful hints or solution would be greatly appreciated. Thanks!!!
I have just loaded my db table from an excel file using the import wizard. Prior to the import, I set my data types and in the field that I need a number with 1 decimal place I chose a decimal data type.
Made since to me
However, now my numbers do not have the decimals.
Please help.
I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!
Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.
Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.
I'm just getting started with SQL 2005 so I don't know how to do this using an expression.
Any help would be greatly appreciated.
Thanks,
Gray
I have a C# app linked to a SQL db and I need to store it's version number in a table (could be something like 1.2.789) but I cannot find any datatype which allows me to do this.
I could create three fields in the table for each number but I don't want to.
For a field in a database to accept both text and number what 'Data Type' should I be using, i.e. 23t5
View 1 Replies View RelatedHi..
I have a column in the data base with the type Float,
I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!?
the number that appear after calculation llike "93.333333"
I use decimal(2,2) as data type but an error accour and this is the message
"- Unable to modify table. Arithmetic overflow error converting float to data type numeric.The statement has been terminated."
Can you help me..
thanks
I have run into a somewhat pain in the posterior situation.
We have an app that currently uses SQL Server authentication. The application also uses a linked server. Now, we would like to move to a Windows authentication type of set up, but from what my network guys tell me is that AD doesn't support a "double hop".
In reading what's out here, I'm getting the impression that Kerberos needs to be enabled or delegation?
Does anyone know of somewhere I can find some good instructions on how to configure my SQL Server(s) to support the double hop?
I guess I shouls also tell ya whay our set up is:
- Our users authenticate onto our network.
- They then authenticate into Citrix.
- From Citrix they authenticate into SQL Server.
- Then there's the linked server.
So essnetially the hops woulg look like this:
Citrix to Database1 is HOP 1
Database1 to Database2 is HOP2
Thanks!!
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies View RelatedDid you any out there used Double Take Software for SQL Server 2000 for Data Protection ( Setting up DR Site )
Thanks
Venu
hello,I have a little (big?) problem with a software in ASP / SQL Server.This is a demo of the problem:ID= 12458 Data=21/01/2004 21:14:45 txt txt txtID= 12458 Data=21/01/2004 21:14:45 txt txt txtID= 12458 Data=21/01/2004 21:14:45 txt txt txtID= 12458 Data=21/01/2004 21:14:45 txt txt txtID= 12458 Data=21/01/2004 21:14:45 txt txt txtSo: I insert a record from my asp page but I find the same record formore times... Why?
View 2 Replies View RelatedHi ,
I'm using sql server 2005. In my 'Books' table some of the 'subject' column contains data's with single and double quote. example: Quantu"m phys'ics
i'm passing the subject as parameter and it have to display results if the corresponding subject name is in the table. Here is my code... i'm using dynamic query...
alter procedure getbooks
@sub varchar(200)
as
begin
declare @sqlq nvarchar(2000)
set @sqlq='select subject from books where subjectname like '''+@sub+'%'''
exec sp_executesql @sqlq
end
How can i search for words like this Quantu"m phys'ics in the above query? Any one who knows how to do this please send me the code..
hi,just wanted to know if i need to insert a string with double quotes init into a sql server table, do i need to use any delimeters, like "?an insert like:insert into producttable values(key, "double quote text")where i need the "double quote text" to go in like that, with the " "at both ends.Thank you.
View 1 Replies View RelatedHi all,I'm reading some doubles from a DB table using c# and some of these doubles are negative numbers.They are negative because the doubles are GPS data and all my Longitude values are, for example, '-6.214545'.Now, I physically put these doubles into the table by typing the '-' character and then the number.What is happening is that the code is reading it as a positive double, which obviously throws off the whole App as the difference between '-6.214545' and '6.214545' is massive!I've declared them as type decimal(18, 6) Any ideas as to what might be the problem???
View 5 Replies View RelatedI am attempting to reach some Clipper tables through a 32-bit ODBC driver from a 64 bit SQL Server. As there is no 64 bit driver offered for Clipper, I am pursuing a solution similar to the one described here:
Creating a Linked Server with 64 bit SQL Server 2008 to MS Access
It involves using a SQL Express 32 bit instance as a bridge.
I have created a Linked Server on the 32 bit instance MTESTXPRESS as follows:
EXEC sp_addlinkedserver @server = N'ABDATA', @srvproduct=N'DataDirect 4.1', @provider=N'MSDASQL', @datasrc=N'ABServerCA'
On the 64 bit instance ALISTESTER I have another Linked Server as follow:
EXEC sp_addlinkedserver @server = N'ABACUS', @provider=N'SQLNCLI', @datasrc=N'ALISTESTERMTESTEXPRESS'
The suggestion is to then use a select statement such as:
SELECT * FROM OPENQUERY(ABACUS, 'SELECT COUNT(*) FROM ABDATA...ABBATCH')
Unfortunately, the DataDirect driver for MTESTEXPRESS will not recognize the 'ABDATA...ABBATCH' 3-part naming convention. The error message is:
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "ABDATA"
Is there some other way to select from the MTESTEXPRESS linked server?
Hi!
Is it possible to double click the sql server express installer, it will install sql server 2005 in silent mode, set sa password..
Just want alternative of command installation on double click
setup.exe /qb INSTANCENAME=<InstanceName> SAPWD=<StrongPassword>
is it possible
thanks in advance
I had a situation that required me to set up SQL Server to do full text search against both English content and Chinese content. I am not sure if it's achievable in SQL server environment. Any help is appreciated.
View 2 Replies View RelatedI have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
I want to get only data in double codes in using sql query.
I have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I have a query question.
Consider a table with the following structure:
RecordID (PK - int) - RecordDate (DateTime)
I need to find all records that fall within a 7 day period slot based on the first RecordDate of a specific slot.
Example, consider the following records:
RecordID - RecordDate
1 - 2015-04-01 14:00
2 - 2015-04-03 15:00
3 - 2015-04-03 16:05
4 - 2015-04-03 19:23
5 - 2015-04-06 09:15
6 - 2015-04-06 11:30
7 - 2015-04-07 12:00
8 - 2015-04-09 15:15
The result of the query I'd like should look something like this
1
2
5
7
8
So basically I'd like to leave record 3 and 4 out because they fall within 24 hours of record 2 and I'd like to leave record 6 out because it falls within 24 hours of record 5.I'd tried working with a CTE and set a dateadd(d, 1, recorddate), join it on itself and use a between From / To filter on the join but that didn't work. I don't think NTILE will work with this?