SQL Server 2012 :: Differences In Rounding / Converting Strings?

Dec 9, 2013

difference between

SELECT ROUND ('6.465',2) --- result 6.46
and
SELECT ROUND (6.465,2) --- result 6.47
with

It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...

Alright:

SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this:
SELECT ROUND('0.285',2) -- 0.28
SELECT ROUND(0.285,2) -- 0.29
SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.

MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:

* how is a string value exactly implicitly converted?

* how exactly does the estimation work, that in case of doubt rounds a value up or off?

View 2 Replies


ADVERTISEMENT

Rounding Differences

Feb 20, 2004

I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?

View 11 Replies View Related

SQL Server 2012 :: Rounding With A Decimal

Sep 3, 2014

I would like the

DECLARE @MyPay decimal(5,9);
SET @MyPay = 258.235543210;
SELECT CAST(@MyPay AS decimal(5,2))

This is what the resultset is currently with the code above:

258.24

I would like to Not have the value round up. I would like to always show only the first two digits to the right of the decimal and not perform any rounding.

View 5 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL Server 2012 :: Results To Show Differences In Items Purchased Versus Sold?

Jun 3, 2014

I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.

Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL

[code]....

I know something like this will give me results but I'd like to run one query.

select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc

View 5 Replies View Related

Need HELP Converting Strings

Aug 23, 2007

I need to convert string data so that I can use it in a view.

example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters

Jeff

View 8 Replies View Related

SQL Server 2012 :: Equal Comparison Of Strings

Jun 10, 2014

Is there a limit on the size of the strings on both sides of the '=' sign for string comparison? If I have two varchar(max) strings, will the comparison be done beyond 8,000 characters?

View 1 Replies View Related

SQL Server 2012 :: Compare Characters Between 2 Strings And Eliminate Similarities

Oct 13, 2015

I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.

Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.

I will give an example below to be more clear

--Start
declare @string1 as varchar(50)='imos'
declare @string2 as varchar(50)='nasos';
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)

[Code] ....

The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas).
So the function should return 3 in previous example.

View 4 Replies View Related

Converting/Casting Strings Into Datetime Datatype

Aug 10, 2007

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?


Thanks!

View 4 Replies View Related

SQL Server 2012 :: Pull Expected Results When Using Strings With Comparison Operators?

Mar 1, 2015

We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:

SQL_Latin1_General_CP1_CI_AS):

USE AdventureWorks2012 ;
GO

--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO

The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".

Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.

USE AdventureWorks2012 ;
GO

--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO

What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?

View 3 Replies View Related

SQL 2012 :: What Are Differences Between AlwaysOn And Clustering

Apr 24, 2015

What are differences between always on and SQL Server Clustering

View 1 Replies View Related

SQL Server 2012 :: Converting XPath To FLWOR

Jun 9, 2014

I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:

create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />

[Code] ....

Here's the working XPath query and results:

select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX

[Code] .....

Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.

select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001

How to build a FLOWR-based query that produces the same result as the pure XPath one?

View 1 Replies View Related

SQL Server 2012 :: Converting Varchar To Datetime?

Mar 19, 2015

I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.

View 9 Replies View Related

SQL Server 2012 :: Converting Nvarchar To Datetime

Jul 24, 2015

I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this

"04/24/2013'
"05/01/2014"...etc...

My requirement is to convert this column into Datetime ?

I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..

View 9 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value

Dec 19, 2013

Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:

CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,

[code]...

Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.

View 8 Replies View Related

SQL Server 2012 :: Inconsistent Results When Converting To Time?

Jun 5, 2014

I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).

These are converted into a smalldatetime like this:

CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]

This code has been in place for years...and we stick the date on later from another column.

But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".

My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.

If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.

I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?

View 9 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Date

Oct 30, 2014

I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.

<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime

[code]....

View 5 Replies View Related

SQL Server 2012 :: Converting Year And Month Varchar To Date

Jan 31, 2014

I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

Year Month Productname
2013 11 ACB
2013 11 CDE

I would now like to add another column called date and store these Year Month as a date to my existing table

Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01

Is there a way I can do it for all the columns of the existing table ??

View 3 Replies View Related

SQL Server 2012 :: Converting Query Output To HTML Format

Mar 23, 2015

I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.

The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.

/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,

[Code] ....

The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.

<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>

If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.

Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.

[Code] .....

I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.

View 6 Replies View Related

SQL Server 2012 :: Converting Large Excel Spreadsheet To Normalized Data

Aug 7, 2014

I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.

Here is a small sample:

Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062

I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.

The desired output:

Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696

and so on ......

The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.

View 9 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value To Data Type Int

Aug 12, 2014

I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.

I am also including example of how the account_numbers are formatted.

R222209
R222220
R222222
R222212
R221123
F707768

[Code] .....

View 5 Replies View Related

SQL Server 2012 :: Float Value Converting To Exponential While Inserting To Varchar Field?

Aug 6, 2015

Am converting varchar field to float and summing using group by and next inserting to varchar field(table).

while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726

My question is why it is converting while inserting ? and how to avoid it.

select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue

View 4 Replies View Related

SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float

Jun 11, 2014

So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.

Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Arithmetic Overflow Error Converting Varchar To Data Type Numeric

Oct 30, 2014

OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.

Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))

View 1 Replies View Related

SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?

Aug 14, 2014

I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

My problem is that Line 6 is:

set @brn_pk = '0D4BDE66347C440F'

so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?

View 3 Replies View Related

SQL Server Rounding Question

May 28, 2004

SQL Server seems to round this down:

AVG(DATEDIFF(d, startdate, enddate))


I've tried using ROUND(AVG(DATEDIFF(d, startdate, enddate)), 0) but it still rounds down.

For example, if the result is 1370.758, I want it to round to 1371.

Am I missing something?

Thanks.

View 1 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

SQL 2012 :: Second Delimiter And Converting

Nov 26, 2014

I have a PL/pgSQL Code like this:

[code=" CREATE OR REPLACE FUNCTION public.split_string(text, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
pos int;

[Code] ....

Its split a String with a delimiter. Like this

[code="select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');

"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220""][/code]

My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the delimiter ','.

View 1 Replies View Related

Differences Between SQL Server Versions

Jul 20, 2005

Hello,I've got installed Win 2003 SBS Premium with the SQL Server 2000 on a servermachine. It works almost fine, except the application which uses the SQLServer. The main part of the application runs (since the last update) fine,but other tools of that application (database import and the databasemanager for check and rebuild) doesn't. They hang up or kill the database.Our software developer says that these problems are in correlation with theserver os. But there won't be any problem if we install Win 2000 Server andadd an additional SQL Server 2000.Finally my question is: Are there any differences between the SQL Server2000 Versions, which are sold (a) as a single product, (b) as part of theWin 2003 SBS Premium package and (c) as part of other Server versions?Thanks in Advance,Martin

View 6 Replies View Related

Differences Between SQL Server 2005 X64 Vs X86?

Mar 12, 2007

Hi there,

I'm trying to find what are the main differences between SQL Server 2005 64-bits and 32-bits. So far, I've found some articles about TPC-C performance but I would like to know some response time or execution time of a batch or SSIS packages.

Any information about this 2 versions is appreciated.

Thanks!

View 3 Replies View Related

SQL Server 2000 Differences.

Apr 19, 2007

Hey all,


I have a VB6 app. If i run it against one SQL server - its fine. Another SQL 2000 server - it fails.

Here is the code



frm.AdodcTemp.Recordset.Delete adAffectCurrent




The error is


Run time error 3021
Either EOF or BOF is true, or the current recordset has been deleted. Requested operation requires a current record.





They are both on Windows 2000 servers. I have checked SQL versions with @@version and



SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')



Both identical.


The i used components checker to check MDAC versions - both 2.8. Also then checked the related MDAC Dlls. All identical.



Only thing i am having real trouble checking is the "Server Network Utility" - which on the server that works (?) is giving me an error of 126 "the specified module could not be found". Any idea what could be causing this?



What else could be different? Any ideas?

Dan

View 5 Replies View Related

SQL Server 2008 :: Incorrect Rounding With Decimal?

Oct 17, 2015

My code is rounding my values incorrectly and I'm not sure why. In this example, the numerator is 48 and the denominator is 49 which is .9795 but my SQL is producing 97.0. I would like to result to be 97.9

CONVERT(decimal(4,1), (SUM(CASE Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +
CASE Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END +
CASE Score_RepAssistance WHEN 1 THEN 1 ELSE 0 END+
CASE Score_Tone WHEN 1 THEN 1 ELSE 0 END +
CASE Score_Consol_Default WHEN 'OK' THEN 1 ELSE 0 END) * 100)
/
SUM(CASE WHEN Score_CorrectID IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_MiniMiranda IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_RepAssistance IS NULL THEN 0 ELSE 1 END+
CASE WHEN Score_Tone IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_Consol_Default IS NULL THEN 0 ELSE 1 END)) AS Avg_Percent_Actions

View 3 Replies View Related

SQL 2012 :: Converting XML Files To PDF And Zip In Folder

Jun 16, 2015

Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.

Can it be possible in SQL server BI world?

If possible make this an automated process for every 100 files.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved