Round Funtion On Entire Columns In MSSQL?
Hi,
I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:
update gbkmut
set bdr_hfl = round(bdr_hfl,2)
can anyone help me?
cheers,
steve
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamically Delete Entire Columns' Values
Hello all, I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data. The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL. I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL. If anyone has any idea how to do this, I would greatly appreciate it. Thanks in advance!
View Replies !
Seeking An SQL Funtion ~ Do You Know IT
Hi All, Please can you help. i a looking for a funtion that will be able to view all the child (many) records in a one to many relation ship and make a list of the parents names....is there such a function??? Example In the one to many contacts db below, if this mystery function ran for "bill" it would say his "parents records" are Gary and Mac Parent Table~ Child Table Gary Bill Tom Harry Mac Bill Jef Tod Charles Les Helen any help here would be much apprecaited kind regards Tony UK
View Replies !
Reg Split Funtion
Hi, Is it possible to split the following value in sql server ? I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database. Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution .. I am using ASP.Net and C# backend is SQL Server 2000. Thanks and Regards Arul
View Replies !
Replace Funtion
how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?! SELECT REPLACE(tasks, '/', '????') AS EXPR1 FROM log_descriptions what should ???? be?
View Replies !
BCP Funtion Problem
Hi, I'm using the function bcp to transfer data from my table to a data file. When I use it at the dos command prompt, no problem everything works really well. But if I use query analyser I have an error: unable to open bcp host data-file. Here's the line: xp_cmdshell 'bcp table.owner.inventaire out c:1est.txt' The problem is not with xp_cmdshell because I can call simpler executables in query analyser.
View Replies !
To_date Funtion YYYY-DD-MM
Hi All: We have a database date column in this format: 1/27/2008 1:20:00 AM and need a date or time function to cast local JVM time into the above format. Suggest me the best options. 1. To go for a more comfortable date format in the database itself or 2. To have an appropriate conversion in my code.( I am not feeling good about the suffix AM/PM in the date column!) Thanks.
View Replies !
REPLACE Funtion For Multiple Values In SP/Reporting Services
Running into a problem with trying to pull multiple values in a stored procedure. Ran Profiler for trace on what variable is coming back as when running in Reporting Services. Profiler shows this: exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier' Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks. What I have: Set @Product= Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + '''')) This is probably a little too much coding but I've tried several ways. In the SP the code is as follows: CREATE PROCEDURE [dbo].[apGetCompanysByRep] @Magazine varchar(6) = null , @DirectsMagazine varchar(6) = null , @Category varchar(50) = null , @SubCategory varchar(50) = null --, @FirstName varchar(30) , @LastName varchar(30)= null , @Product varchar(1100) AS declare @SQL varchar(2000) Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + '''')) set @SQL = ' Thanks for any assistance anyone can give.
View Replies !
T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)
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 Replies !
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Hello: I am running into an issue with RS2k PDF export. Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span . User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width. We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect. Any help or suggestion on this issue would be appreciated
View Replies !
Round Up And Round Down In Sql
I want to do a simple thing but it seems to be behaving not as i am expectingI want to round number either up or down....e.g: 4.3 should round to 4 4.7 should round to 5when i use the round function like this: 83/17=4.88round( 83/17 , 0 ) it gives the answer as 4....when i was expecting it to be 5.... i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down. how can i do this? hope this makes sense. thanks
View Replies !
Round Up
X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL?
View Replies !
Round ?
I have the statement below which I use in an update select (round(sum(tottime/60),2)) as ttime from vw_cms_suptime where vw_cms_suptime.[tracking number] = 970 tracking.[tracking number] Even though I have the round the statement returns a value of 5.0000000000000003E-2 Is there something wrong with the round?
View Replies !
Round Bug
Has anyone been experiencing problems with rounding to 2 decimal places in SQL 7? I have a bunch of queries that generate web reports. Under 6.5 everything was fine. Now under 7.0 any number that needs to be rounded to 2 decimals is actually giving me several decimal places. Here is a simplified version of what I am doing: DECLARE @x real DECLARE @y real SELECT @x = 223.1 SELECT @y = 59.7 SELECT ROUND((@x/@y),2) Result should be 3.74. But instead I am getting 3.7400000000000002 If anybody has heard if Microsoft has declared this as a known bug please let me know. Thanks for your time.
View Replies !
Round Up
X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL?
View Replies !
Round
There is a filed in a table of type decimal(12, 8) when running a select query on this table for this field i.e. select field1 from table1 the data is shown like: 102.12500000 104.12500000 And therefore the report shows the same figure. How do I get this field to show up in the report as 4 decimal places. i.e. 102.1250 104.1250 Tried the properties of the cell to format the text into a number but there is only 2 decimal places there. Even tried the expression by using Rnd(field1). This does not seem to do what I am after. Thanks
View Replies !
How Do I Round The Value ?
Hi I have datacloum called 'price' (float) and using the below code in my stored procedure cast(price * 1.175 as decimal(19, 2)) as [item-price] I am getting the price as (ex1: 23.58, ex2: 114.25, ....etc) So How do I round the value(price) to (ex1: 23.99, ex2: 114.99) Advance thanks
View Replies !
Round Off
how do i round off value 0.23 to 0.2. i am using this inside a scalar function and the return type is numeric(6,2). so how do i get 0.2 and not 0.23
View Replies !
Round Off
How to round off the value .579 into .6 using round function.i tried doing it but in vain.
View Replies !
Round To Near Value
hi,i have one criteria if if i have time as 1:15 min means it has to show 1:15,if i have 1:20 min it has too be rounded and it has to show 1:30 min. can any one give me query for this thanks in anvance
View Replies !
Round The Value
Hi, I need to display the value of a certain varible rounded to two digits after decimal. For example : value of a is 1346.8500 I need to get the value of a as 1346.85 Please help me
View Replies !
Round Problem
Hi, I am using sql statement to save data in SQL SERVER but even i did not apply any round function it is automatically rounding up. e.g. 3.56 when i see it in database it is 4 how can i avoid this rounding? I am using MS Access as front end. Any help will be highly appreciated.
View Replies !
Cast And Round
I am using a statement in which a where clause has a statement like this Where .. and cast(round(colname,1) As Numeric(9,1)) =>9.5 When i execute this query i get this error Server: Msg 8114, Level 16, State 5, Procedure sp_rpt_get_count_hb_great_9andhalf, Line 22 Error converting data type varchar to float. Any help is appreciate
View Replies !
Round A Number
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
View Replies !
Round() Problem
Hi, When I use the following query select round(7.35,1) the output is 7.4. But when I use this query declare @tot float set @tot = 7.35 select round(@tot,1) the output is 7.3 How do I make the output consistent here desirable is 7.4. Please advise.
View Replies !
Can't Seem To Round An Average.
I am using an expression to create an average. The result is an odd number like 33.666666666666667. I want to round this to 34 (I would even accept 33, or 33.67), just not so manny digits to the right.
View Replies !
Rounding - Always Round Up
Hi All, Is there a way in T-SQL using the round command to always round up regadless of the value. For example:ROUND(normal_hours * pay_rate * 52, - 3) AS Expr3 normal_hours = 36 pay rate = 23.64 weeks in year = 52 (36 * 23.64) * 52 = 44,254.08 It rounds to 44,000. I want 45,000. Is this possible. Am I overlooking the obvious? Thanks in Advance and Happy Holidays!!!!!!!!Adam
View Replies !
Problems With Round()
I understand that when you use ROUND(8.5,1), I would get 9 but no matter how hard I try, I get 8. I had this code that suppose to get the round of a quotient. I wanted to update a number of records by converting them via multiplication and/or division. Here: UPDATE Length SET inchTOcm = inchTOcm*2.5, metricTOton = ROUND(metricTOton/1.1,1); supposing the original value of inchTOcm are in inch and in metricTOton are in metric ton. There is a value in the metricTOton that when I divide it with 1.1, it's 345.81818. However, when I round it, it displays 345 instead of 346. Is there wrong? How can I do this without using the CASE statement?
View Replies !
Get Round Of Getdate()
Hi, i write a query select getdate() suppose output come '2008-03-01 14:08:52.187' i want to get like this output '2008-03-01 14:09:00' means want to neglate second and milisecond part and want round of getdate() Ranjeet Kumar Singh
View Replies !
Round Function
I have a money field, which I want to Round price in it to nearest .95 cent . The problem I have is, I don't know how to give 0.95 to Round function. Thanks Mazdak
View Replies !
How To Round With Negative Numbers?
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob
View Replies !
ROUND Unexpected Result
/*********** Script 1 **************/ declare @nr_1 as decimal (10,2) declare @nr_2 as decimal (10,2) set @nr_1=5 set @nr_2=3 select round(@nr_1/@nr_2,0) RESULT = 2 /*********** Script 2 **************/ select round(5/3,0) RESULT = 1 What it is the explication for these difference ?
View Replies !
Strange ROUND Function!!!
Set @AVG = ROUND ((V1*5+V2*5)/10,0) What is the difference between these codes? Set @AVG = ROUND ((V1*5+V2*5)/10.0,0) For values V1=80, V2=85; First code gives 82! Second gives 83!:confused: :confused: :confused:
View Replies !
CAST And ROUND (MS SQL 2005)
for MS SQL 2005 I am having SELECT name.Name , Percentage = (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) FROM dbo.Name GROUP BY Name how can i CAST and ROUND (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) ? then ROUND (the result, 2) ? CAST(count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) AS DOUBLE does not work thank you for helping
View Replies !
SQL Query - 'ROUND' COMMAND
Has any of you ever had problems with the ROUND command? I have this operation: ROUND((FIELD1 / 360 * FIELD5),3) AS FIELD7 (I want the result to be rounded at the third decimal). SQL makes mistakes in rounding. It seems to me that the problem relies in the quantity of decimals taken into account in any single step of the operation. Thank you in advance. Anna - Verona (Italy)
View Replies !
ROUND Function In Ms-sql Server
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
View Replies !
Run Entire Package
Hello, i have a problem that i don't understand why it occurs: i have a package like this (resumed and ordered by its correspondingdata flow): 1. move data files to a path through by a file system task 2. import data of data files to a sqlserver database (each file through its own data flow). The origin of data is in files that have been moved to the path of step 1 3. execute a procedure of database (sql task) to manipulate data and finally export resume (data flow) to a new file in the path (moved by other file system task) PROBLEM: if i execute the package task by task (one by one), it runs ok, but then when i build and run package as a whole (debug), it produces an error in the data flow such as it doesnt' find the path to take data of data flow (it doesn't find the source). Really it doesn't find source because the data files aren't in its correct path (like step 1 had failed). In summary, it looks like if the order of the task of package aren't correct when i execute the entire package, because the log doesn't thread any error about step 1 (file system task). ERROR MESSAGE: an OLE DB error has occurred. Error code: 0x80040E37 Thanks for any help. Gema
View Replies !
Query Entire DB
I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria? This is what I want: Select * From [db] where [column] like '%123456%' I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.
View Replies !
Executing N Procedures In 1 Round Trip
w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @Campaign, @Contact 20 times how would I pass in the values for those 20 diffrent versions?
View Replies !
Round Up Float In SQL Server 2005
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
View Replies !
Problem With Round Function In TSQL
Trying below instructions create table t ( indexvalue float ) go insert t values (109.1) insert t values (109.3) insert t values (109.5) insert t values (109.9) go select * from t select sum (indexvalue) / count (*) from t select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect from t --why round function doesn't work correctly in this select select round (109.45, 1) -- but this line results 109.5 that is correct
View Replies !
|
TRACKER
