SQL Server 2012 :: Positions After Decimal Point

Oct 21, 2014

Why are there more decimal positions here

PRINT cast(111 as decimal(38,35)) / 23
--> 4.82608695652173913043478260869565217

then here

DECLARE @Zähler decimal(38,35)
, @Nenner decimal(38,35)
SET @Zähler = 111.0
SET @Nenner = 23.0
PRINT cast(@Zähler as decimal(38,35)) / @Nenner
--> 4.826086

Of course, in the upper part 23 is implicitly an integer, in the lower example it is declared as decimal. But what if I need to devide by 23.5? Why is dividing by an decimal reducing the results decimal positions?

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Trim Values If More Than 2 Numbers After Decimal Point

Jul 23, 2015

I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?

I know there is the rounding function that could be used like so:

SELECT ROUND ([Hrs Total 2],2)
FROM AnnualClassifiedPAFs

How do I then take that rounded value and insert it back into the records?

View 2 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Point Shifting On SUM

Oct 27, 1998

The following SQL statement returns the correct totals except that
the total value is shifted on decimal place to the right. i.e. a real
total of 955.68 is displayed as 9,556.80. The total_ar field is a money type.
Any help would be appreciated.

Mark

/* AR report Total Greater than 365 days sorted by Dept */

select

a.dept as `Department `,
` A/R 365+ `=sum(case when datediff(day,c.bill_date, getdate()) > 365 then b.total_ar else 0 end)

from

hbm_persnl a, blt_billm b, blt_bill c,hbl_dept d,hbm_matter e

where
e.matter_uno = b.matter_uno and
a.empl_uno = e.bill_empl_uno and
b.bill_tran_uno = c.tran_uno and
b.ar_status = `O` and
e.status_code=`OPEN`

group by a.dept

View 1 Replies View Related

Decimal Point Problem

Jul 29, 2004

I want to force two places to the right of the decimal even if its a whole number.

vbScript

calculatedScore = FormatNumber((rsQFinal("sumEarned") / rsQFinal("sumPossible")) * 100, 2)


I'm not savy enought to know the SQL equivalent of the above but I'd like SQL Server to perform the above rather than my vbScript code.

heres what I have so far but I don't know the formatNumber equivalent:

CREATE PROCEDURE quarterFinalGradeSA @nClass INT, @nQuarter INT, @nStudent INT AS

SELECT (SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints)) AS returnValue
...

View 4 Replies View Related

Problem With Decimal Point

Nov 28, 2006

I have got txt file and I am trying to import this file into the database. For that action I use SQL Server Import and Export Wizard. I use Locale Czech and CodePage 1250. In the text file is the column in that format: 18152.65 - it is number with decimal point. When I use BCP utility for importing data, I use datatype decimal(10,2) and everything is OK. But when I try to use Import and Export Wizard, I choose for that column datatype numeric (DT_NUMERIC - precision 10, scale 2), the Import doesn't start and occurs the error:

- Executing (Error)



Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "PRED_CEL " returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

View 2 Replies View Related

Change : To Decimal Point In Cast

May 20, 2008



I have the following code:




declare @number nvarchar(50)

set @number = '7:50'



select cast (@number as float)

This gives the error:


Msg 8114, Level 16, State 5, Line 8

Error converting data type nvarchar to float.


But if I change the value to '7.50'
it works ok.

Is there any way to get the cast statement to interpret the : as a decimal point?

Thanks,

View 2 Replies View Related

How To Remove Decimal Point From Number Without Rounding

Aug 30, 2013

How do you display 12.38 as 1238 I can't use round.

View 2 Replies View Related

Reporting Services :: Format With Decimal Point In SSRS

Jun 13, 2015

I have a problem with my reports in SSRS 2012,

I have numeric values in database(10.23) but when load this values in a report, I see: 10,23 instead 10.23 

The regional configuration of server is: Spanish-Peru. Why the report show the value: 10,23 I don't put any regional configuration in the report...

View 3 Replies View Related

Limit The Number Of Digits After Decimal Point In Flaot Data Type

Dec 12, 2007

Hi..
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
 

View 6 Replies View Related

SQL 2012 :: How To Deploy SSRS Report Into Share Point Server

Jun 3, 2014

I am trying to deploy the report into share point server through Visual Studio by using the [URL]..... But I am getting errors like [URL] .... could not be found.

I configured the the URL credentials like :

Target Datasource Folder: DEV/MyReports
Target Dataset Folder: DEV/MyReports
Target Report Folder: DEV/MyReports
Target Server URL: http://bigweb03

How can I deploy the reports in Sharepoint server to view the reports in online.

View 0 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 :: Padding Zeros After Decimal

Jan 29, 2014

I have a table like

[A]-------------[B]
[0]-------------[0.012345]
[1]-------------[0.002345]
[0.2145]------[0.1457]

I need both columns A and B to be padded with zeros at the end upto 9th place after decimal so the output should look like:

[A]------------------------[B]
[0.000000000]-----------[0.012345000]
[1.000000000]-----------[0.002345670]
[0.214500000]-----------[0.145700000]

Note: total number of digits = 10 not including '.'decimal

I tried this but just getting 11 zeros.. it work when i want zeros in the left but not on the right

select right(REPLICATE('0', 11) + CAST([table] AS VARCHAR(11)),11)

from #table

View 4 Replies View Related

SQL Server 2012 :: Geography Data Type - Test If Point Is Inside A Closed Polygon

Dec 5, 2013

I have a closed polygon that coincidently is in the shape of Iowa :) I have a point that is within the state and a point WELL outside it, but I get weird results that I don't expect when I try to get it to tell me that the point is within the polygon. Here is some basic code, with long coordinates data.

DECLARE @g geography,
@pIn geography,
@pOut geography

SET @g = geography::STPolyFromText('POLYGON((-91.119987 40.705402, -91.129158 40.682148, -91.162498 40.656311, -91.214912 40.643818, -91.262062 40.639545, -91.375610 40.603439, -91.411118 40.572971, -91.412872 40.547993, -91.382103 40.528496, -91.374794 40.503654, -91.385399 40.447250, -91.372757 40.402988, -91.385757 40.392361, -91.418816 40.386875, -91.448593 40.371902, -91.476883 40.390968, -91.490158 40.390762, -91.500221

[code]...

(1 row(s) affected)As I read that there is a distance of about 7864 meters, this is close to what I would expect, so that's ok. The point outside I would expect a distance as well so that is confusing.. Then we have the intersects, it says that the point inside does NOT intersect but the one outside DOES, this is backed up by the intersection values.

View 1 Replies View Related

SQL Server 2012 :: Decimal Data Type Round Off The Values

Sep 11, 2014

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

View 2 Replies View Related

SQL Server 2012 :: CSV Import With Non-character Data (Date And Decimal)

Mar 29, 2015

I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.

The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.

<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>

The SQL Column is defined as Decimal ((15,2), NULL))

I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values

INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;

I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.

View 1 Replies View Related

SQL Server 2012 :: Insert Empty String In A Decimal Field?

Jul 13, 2015

I have a requirement where i have to insert empty string in column whose datatype is decimal.

I have to replace the column value if it is null then insert it as ' '.

Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.

IIF(Column1 IS NULL, ' ', Column1))

I used TRY_PARSE but it insert null value if there is conversion error.

View 3 Replies View Related

SQL Server 2005 Mgmt Studio: Save Table Vew Positions

Nov 28, 2006

I'm in stage of manually populating a database that requires me to have about a dozen tables open simultaneously. It would be great to be able to save the tables, locations and sizing so that these can be recalled and opened automatically. Currently I do a screen capture, which is pretty lame.

Any suggestions would be welcome.

Config:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

View 3 Replies View Related

SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage

Dec 4, 2014

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.

I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?

select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id

id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

View 5 Replies View Related

Importing Data From Oracle To Sql Loosing Data After The Decimal Point

Jun 18, 2007

I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.

In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.

thanks

View 6 Replies View Related

SQL 2012 :: Determining Which Polygon Contains A Point

Nov 5, 2015

I have a table that contains spatial points and the name of the polygon they belong to (geography and varchar columns). I need to write a function to accept a point and determine which polygon contains that point. How can I write the query so that it will search through each polygon (derived from the geography points found in the table) and return the name of the polygon that contains that point?

View 2 Replies View Related

SQL 2012 :: AlwaysOn - Access Point For Administering?

Oct 5, 2015

Node 1 -IP Address 10.xx.xxx.11
Node 2 -IP Address 10.xx.xxx.12

while create cluster wizard in access Point for administering the cluster which IP address need to type Address filed? Does it Node1 IP to be type?

1. I have type Node 1 -IP Address 10.xx.xxx.11 in that filed but I Received error as below

You do not have permissions to create a computer object in the Active Directory

2. What type of permission is required that particular domain id which was logged both the nodes?

View 8 Replies View Related

SQL 2012 :: Restore MDF Then Apply Logs / Point In Time

Jun 6, 2014

Our backup system has worked ok for us to date. We can restore back to either full saves or up to a certain log (we take log backups on the hour). We've never had to, but wanted to test restoring to a point-in-time with the backup data.

What the system does is generates .mdf and .ldf files, which is essentially a full backup say in the middle of the night. It then creates .bak files for the log backups based on the backup set you want to restore.

I can detach the database and apply the .mdf and .ldf and re-attach the database, but to apply the .bak files I need to get the database into a (recovering) state. I can't seem to do that. Otherwise when I try to apply the .bak files the system says: The log or differential backup cannot be restored because no files are ready to rollforward.

How to apply a .mdf and then apply the .bak files?

View 7 Replies View Related

SQL 2012 :: Performing Point In Time Recovery With Replication?

May 6, 2015

How do I recover point in time if the database is participating in transactional replication and is a publisher.

View 0 Replies View Related

SQL 2012 :: Won't Convert To Decimal

Feb 3, 2015

I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name "Length" set up as Decimal(18,2) and it stops on the first row with an error, something like "Cannot convert to decimal". The values are all integers, such as "9", etc. but I presumed SQL would convert to "9.00" for example. It has worked for another field name "Weight", where values are stored in the XML file such as "0.28", etc. Does it reject it because it's an integer and needs to be to two decimal format in the XML?

View 9 Replies View Related

SQL 2012 :: Decimal Value - Output In Lakhs

Mar 3, 2014

I have a decimal value, 10,55,263.55 . This value should be converted into lakhs and the output should show as 10.55. I need to display in lakhs. What is the query do I need to write to get this output.

View 2 Replies View Related

Are There Any Entry Level DBA Positions?

Nov 8, 2007

Hi guys,

I have just started studying for an MCTS, to eventually get to MCITP for SQL Server 2k5.
I am currently in an Application Support position, working with a large clinical Oracle Database, but mainly on a simple querying basis.
The plan is obviously to get away from the Tech Support, towards the DBA and I have been researching the current job market and never once found a job offer that said "offering an entry level or Junior DBA position for those that are just starting"... There's always at least a few years of experience already as a DBA required!

I am wondering how one is supposed to get that experience if he doesn't get a beginners position to start off with?

Any stories from experience? Any hints where to look, how to apply and where maybe?

Thanks,
-free-

View 3 Replies View Related

SQL 2012 :: Convert Division Of Int Result Into Decimal

Jul 4, 2015

Which one is good method to convert a following division into 2 decimal digits.

q1)

select cast(( cast( sum(population) as decimal) * 100) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state

q2)

select cast(( sum(population) * cast( 100 as decimal)) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state

q3)

select cast( (sum(population) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q4)

select cast(( cast(sum(population) as decimal) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q5)

select cast( (cast(sum(population) as decimal) * cast(100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q6)

select cast(( cast(sum(population) * 100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

View 5 Replies View Related

SQL Server Over Point-to-Point T1

Nov 10, 2006

I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?

Thanks,
Peter

View 7 Replies View Related

Is Point In Time Recovery To A Point Before The Last Full Database Backup Possible?

Mar 26, 2008

Hello all,

First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.

Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.

Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.

To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.

I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.

My questions are as follows:

a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?

b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).

I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.

Ryan

View 4 Replies View Related







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