SQL Server 2012 :: Odd Cast Of Exponential To Float?

Mar 23, 2015

For whatever reason i'm unable to cast anything more thtan e-4 to a float which makes no sence. Am i missing something?

select cast( '1.550e-6' as float)
?????????
returns 1.55E-06
????????
select cast( '1.550e-5' as float)
?????????
returns 1.55E-05
????????
select cast( '1.550e-4' as float)
returns 0.000155
select cast( '1.550e10' as float)
returns 15500000000

View 9 Replies


ADVERTISEMENT

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

Cast From Float To Varchar

Aug 1, 2006

Hi,

Can I convert from float to varchar without trunc the values? Can I use any mask like '#.##'?

from -> cast ( 123.44 as varchar(256) )

result = '123.44'

thanks,

View 1 Replies View Related

SQL 2012 :: Save Real Datatype Without Converting To Exponential?

Sep 21, 2015

I want to save 999999999 as real data in sql.but it saved 1+E09.

how can I save 999999999?

View 9 Replies View Related

SQL Server 2012 :: Using CAST Or Convert Functions

May 31, 2015

How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).

Calculation: @Diff = (@EndTime - @StartTime)

I still want the variables (@StartTime and @EndTime) to remain as nvarchar.

The code:

DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';

View 7 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 2012 :: Convert Date To Float

Apr 2, 2014

What actually happens when I convert date to float? Select convert(float,getdate(),112)

View 1 Replies View Related

SQL Server Integration Services - Stop Large Number From Being Converted To Exponential

Sep 23, 2006

I am importing a text file with a column (serial numbers) with alphanumeric data, some mixed and some only numeric. The very large values that are all numeric are being converted to exponential when I run it thru an import package in SQL Server Integration Services (2005)

Ex. 4110041233214321 --> 4110040000000000 (displays as 4.11E+15)

In the past I dealt with this by importing the text file into Excel and changing the format of the column to number. This works even when many of the values contain alpha characters. I am not sure how to accomplish this same thing without going thru Excel. If you have any ideas on this I would be happy to hear from you.

I am importing the text file into a sql table.

View 1 Replies View Related

SQL 2012 :: Unable To Cast COM Object

Dec 31, 2014

I have already registered the Dts.dll, but it did not make any change, also I have update the visual studio to latest update 4.I have registered the dll by using CMD to the below location and entering regsvr32 dts.dll, it brought up a popup message saying the dll the has been registered.

C:Program FilesMicrosoft SQL Server100DTSBinn

But the issue remains the same, I have to click ok on the popup msg for about 20-30 times to make it go away and the dtsx package in the solution work just fine after closing the pop up.

unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{203E24BD-5203-4C76-AFC7-7C8545EBA1A0}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)). (Microsoft Visual Studio)

View 4 Replies View Related

SQL 2012 :: Cast Varchar To Bigint Error

Mar 4, 2014

I try to run code but got an error. How to fix it?

SELECT Cast(('20140304800084500001') AS BIGINT)

Arithmetic overflow error converting expression to data type bigint

View 1 Replies View Related

Convert A Binary Float To FLOAT Datatype

Apr 9, 2007

I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.

All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little


Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)

SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))

SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]

And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT

SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END

RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!


Peter Larsson
Helsingborg, Sweden

View 3 Replies View Related

Exponential Value

Aug 28, 2007



I am using data flow. source is ole db and target is flat file (csv). I run sql server stored procedure in source and mapped all columns to target file.

Value "-5.0000000000000003E-2" is giving me hard time. It's coming in target file how can remove exponential before writing to target file. In source table that value is coming from float type column. I would like to use some function in select sql if I can.

Thank you - Ashok

View 7 Replies View Related

Exponential Moving Avg.

Jul 20, 2005

Exponential Moving avg is calculated using the formula.EMA = (Today's Price)* K + (EMA yesterday) * (1-K)where K = 2 / (N+1)The user is going to Input the K.It is something likeF(N) = Price * K + F(N-1) * (1-K)How can I reference, the previously calculated value in the Next rowcalculation. I need to implement this in SQL Server.I created a Stored procedure to do this and I used a Temp tbale withIdentity.Create Table #TempMovAvg(MID int identity(1,1) Primary key, tempDate DateTime, tValue float)I Populate the data for that temp table using the below query.Insert Into #TempMovAvg (tempDate, tValue)Select Date, Price From DataTableI tried Diff options to calculate the exponential Moving Avg using theabove formula, but none of them are giving the correct answers. I amnot able to reference the Prev Calculated value in the Next rowcalculation.Some queries I used.Select a.TempDate, a.tValue,0.9*A.tValue+0.1*(Select 0.9*t1.tValue+0.1*t2.tValue From #TempMovAvg t1, #TempMovAvgt2 Where t1.MID=A.MID and t2.MID=t1.MID-1)FROM #TempMovAvg AWhere A.MID>=2order by a.TempDateSELECT A.MId,SUM(CASE WHEN B.MID=A.MID THEN 0.9*B.tValueELSE 0.1*A.tValue END) exponential_averageFROM #TempMovAvg A, #TempMovAvg BWHERE A.MID>=2 AND A.MID BETWEEN B.MID AND B.MID+1GROUP BY A.MIDAny help will be greatly appreciated.thanksGanesh

View 1 Replies View Related

Exponential Database Autogrowth

Dec 7, 2007

Whenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated

View 1 Replies View Related

Exponential Values In The Report

Sep 3, 2007

Hi,
I am values in scientific notation when i am rendering the report into excel.
I wanted the number to be displayed as it is without any scientific notation(exponential format)

Thanks in advance
Nalini

View 3 Replies View Related

SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?

Dec 26, 2014

I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:

Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')

how this can be done while my temp table is in session?

View 2 Replies View Related

SSIS Package (Data Flow) Validation Time Going Exponential

Jul 25, 2007

I am using SSIS to populate a star schema.

The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine). After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow. The problem is not in execution; the problem is adding more transforms in design mode.

Lookup # Fields in Data Flow Time to validate that lookup
<17 47 Sub-second
17 48 2 sec
18 49 4 sec
19 50 8 sec
20 51 16 sec
21 52 32 sec
22 53 64 sec

While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform. I hope you can see my dilemma.

I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes). Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.

Some notes:
1. It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2. I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)


I realize this may be somewhat of a unique problem.



Thanks for any help you are able to lend.



-Dave

View 3 Replies View Related

SQL Server Float Data Type

Feb 16, 2006

1. In c++ (or any other language) I'm copying bit by bit to a doublevariable dob_var.2. If I copy dob_var to dob_var2 which is also a double I do not loseinformation.3. This implies that the same would be when copying to a data base, butwhen I write dob_var to an sql server float field (same size as adouble variable = 64 bit) it sometimes loses information. e.g. whenvalue=1E-320.It is true that SQL float is valid until 1E-308 but so is a C++ double.Any comments?

View 2 Replies View Related

Round Up Float In SQL Server 2005

May 1, 2008


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 5 Replies View Related

SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.

Example

formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies View Related

SQL Server 2008 :: Difference Between Money And (Float Or Decimal) Datatype

Jan 16, 2013

What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?

View 4 Replies View Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

Disabled The Automatic Round Of SQL SERVER 2000 On The Float Data Type

Jun 19, 2007

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

View 2 Replies View Related

Secure A Single SQL Server Database + Make Float Fields Encrypted

Mar 21, 2007

Hi all,

I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.

I have two questions:

1) First of all (if it is possible) I would like to protect my own database from the other companies.

I don't want them to:


see the data in the tables (around 20 tables)
make changes to the stored procedures (more than 100 stored procedures)
be able to backup the database

2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.



Thanks for your patience reading this!

Would really appreciate some help on any of these

Nicolas

View 4 Replies View Related

How To Cast The Value In C# Resulted From Max() Command In SQL Server Developer

Aug 1, 2007

Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?
I have tried:
- string test = (string)reader["MaxOrderID"];
- int test = Convert.ToInt32((string)reader["MaxOrderID"]);
- string test = Convert.ToInt32(reader["MaxOrderID"]).ToString();
all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions? 
For example, I have the following code:command.CommandText = "Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType = CommandType.Text;
 
command.Connection = conn;
command.Connection.Open();
reader = command.ExecuteReader();
reader.Read();
??? orderID = ???reader["MaxOrderID"];
 

View 5 Replies View Related

SQL Server Admin 2014 :: How To Convert Float To Timestamp In Single Select Query

Mar 18, 2015

How to convert float to timestamp in single select query

E.g., i have float as 1.251152515236 ,

I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00

View 1 Replies View Related

SQL Server 2008 :: CAST (INT AS DATETIME) - Random Numbers

May 20, 2015

While trying to solve a SQL challenge I found myself trying to understand what is happening when you CAST a INT to date time.

Trying to understand the results. Here are some random numbers and Castings. My question is why do they produce the datetimes they do?

SELECT CAST((1.1) AS DATETIME)
SELECT CAST((200) AS DATETIME)
SELECT CAST((15) AS DATETIME)
SELECT CAST((99.99999) AS DATETIME)

View 9 Replies View Related

Invalid Character Value For Cast Specification: SQL Server 2000

Jul 20, 2005

Hi,I used SQL Server 2000 Personal Edition. I created a stored procedurehaving input parameters as smallint,tinyint,char,varchar andsmalldatetime. When I executed it by Query Analyzer, it's ok. But whenI executed it by ASP code that used ADODB.command, it showed error:Invalid character value for cast specification .Though SQL Server debugger, it showed:[Microsoft][ODBC SQL Server Driver]Invalid character value for castspecificationWhat did I do wrong? How do I fix this problem?TIAKrungPS: One of input parameters was smalldatetime, but I casting it in ASPas date as:Const adDate=7datetxt='2004/6/28'cmd.Parameters.Append cmd.CreateParameter("CoopId",adChar,adParamInput, 8, coopid)cmd.Parameters.Append cmd.CreateParameter("Addr",adVarChar,adParamInput, 100, addr)...cmd.Parameters.Append cmd.CreateParameter("Cdate",adDate,adParamInput, , datetxt)....cmd.execute

View 3 Replies View Related

SQL Server 2014 :: CASE Condition Not Affecting CAST Output

Jun 10, 2014

I've a variable in my SP that I'd like to use to change the format of my output. Try this!

select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Then this:

select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):

select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2

Work fine.

View 8 Replies View Related

Real Figure Instead Of Exponential Figure Needed

May 29, 2008

Hello,

I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.

Example: 1225000.00

When I select this value using SSMS I get the correct value:

1225000

Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))

1.225e+006

I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.

Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?

Thanks: Peter

View 5 Replies View Related

Float Or Int

Feb 18, 2004

Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?

Thanks for any thoughts,

View 5 Replies View Related

How To Set Float Precision

Feb 20, 2000

Hi!
I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?

Regards,
Sam

View 1 Replies View Related

Float Precision

Sep 5, 2002

Hello everyone,

I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)

Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.

Thanks,
Randy

View 1 Replies View Related







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