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,
Hello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
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
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
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 RelatedHi 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!
I'm reviewing the CAST function using Microsoft SQL server. I generally understand the functionality, but my confusion lies in the results of a particular script which casts a DateTime value to a fixed-point number.
It is as follows:
DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)
SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))
PRINT @To
This results in: 40095.45833
I am completely lost as to how 40095.45833 = 2009-10-11T11:00:00. I just do not understand the how that fixed point number equates to that source DateTime data.
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
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
...
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)
Hello, The issue is to convert a number like 114270 to 114 + (270/320) = 114.84375. The decimal version is the desired result. In other words the last 3 digits of 114270 are in base 320.
Question: I cast my number as char() then use substring() to get the digits. Then I cast back to float and divide by 320. I get 0 when I do this and I'm not sure why.
Code follows:
declare @p float
set @p = (select top 1 P from [tablename] where Product = 'Z')
declare @pchar as char(6)
set @pchar = cast(@price as char(6))
declare @first3 as char(3)
declare @second3 as char(3)
set @first3 = cast(substring(@pchar, 1,3) as float(25))
set @second3 = cast(substring(@pchar, 4,6) as float(25))
select @pchar -- 114270
select @first3 -- 114
select @second3/320 -- 0
I have tried changing cast(substring(@pchar, 4,6) as float(25)) to
cast(substring(@pchar, 4,6) as decimal)
and I get the same result. Any suggestions or is there a better way altogether?
How do you display 12.38 as 1238 I can't use round.
View 2 Replies View RelatedWhy 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?
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...
All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.
For example:
Database Price SELECT statement Price
100.56 100.5600
I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?
Thanks
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?
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
I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROMÂ PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
I have data and graphs that I have in Excel and am porting to sql 2005 in Excel I have option to change plot area back ground color and the label background color to white. Why isn't there a fillcolor selection capability in the EditChartValuePointLabellabelStyle dialog box?
is there a workaround with the format code textbox?
Thanks
i am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
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
We have created a chart in SharePoint 2013 with performance point dashboard designer. We want to change Title of chart. For more  information please check attached image.
View 2 Replies View RelatedHello 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
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
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
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedAnybody 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 RelatedWe ran into weird/interesting issue with below details.
Version:Â Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)Â Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
Hi
Can anyone tell me how I can CAST these fields AS DECIMAL (19, 2) please?
(SELECT ([Total students] - [withdrawn] - [transferred] - [cancelled]) / [total Students] * 100 AS [Percentage Retained])
Thanks
Daniel