Converting dates into floats

I have an odd database that has a date stored in a float field. The
dates originate in an external database, which is imported record-by-
record, with data cleanup and conversion, into SQL Server. We do many
of these sorts of conversions, but normally they are put into a
datetime field instead of a float.
Everything seems to work perfectly under VB. However, I am in the
process of converting from VB into SQL for a variety of reasons. This
floatdate is causing a problem, as it is always off by two days. Let
me give you an example...
On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
read in the date from the external database, which stores it as a
string: "20080605". Here is the code I use to convert it...
Function RealDate(Datestring) As Date
Dim Yr As String
Dim Mth As String
Dim Dy As String
On Error GoTo notadate
Yr = Left(Datestring, 4)
Mth = Right(Left(Datestring, 6), 2)
Dy = Right(Datestring, 2)
RealDate = Mth & "/" & Dy & "/" & Yr
Exit Function
notadate:
RealDate = 1 / 1 / 1900
End Function
So far so good. I then put that result, a VB Date, directly into the
float field in the database. If I then read that back out in VB and
cast it to a date (which is automatic if you want) I get back the same
value.
However, when I do this in SQL, I get a _slightly_ different date:
cast(price2 as datetime) as expiry
returns 2008-06-07 00:00:00.000
It's off by _two days_. At first I thought this was an epoch issue.
Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
Server uses 1/1/1900. Is this understanding correct? If so, how is it
that the resulting date in SQL is only off by two days, and not 70
years?
MauryMaury,
SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
somewhere along the line) that Visual Basic's zero day is 1899/12/30. (I
believe it was supposed to be 1899/12/31, so that makes two mistakes, one
for each day that your calculation is off.)
I am relying on memory since I cannot find the reference right now.
RLF
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:e8ffe61e-6b41-4b1c-b1ce-f25890d22ece@.d1g2000hsg.googlegroups.com...
>I have an odd database that has a date stored in a float field. The
> dates originate in an external database, which is imported record-by-
> record, with data cleanup and conversion, into SQL Server. We do many
> of these sorts of conversions, but normally they are put into a
> datetime field instead of a float.
> Everything seems to work perfectly under VB. However, I am in the
> process of converting from VB into SQL for a variety of reasons. This
> floatdate is causing a problem, as it is always off by two days. Let
> me give you an example...
> On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
> read in the date from the external database, which stores it as a
> string: "20080605". Here is the code I use to convert it...
> Function RealDate(Datestring) As Date
> Dim Yr As String
> Dim Mth As String
> Dim Dy As String
> On Error GoTo notadate
> Yr = Left(Datestring, 4)
> Mth = Right(Left(Datestring, 6), 2)
> Dy = Right(Datestring, 2)
> RealDate = Mth & "/" & Dy & "/" & Yr
> Exit Function
> notadate:
> RealDate = 1 / 1 / 1900
> End Function
> So far so good. I then put that result, a VB Date, directly into the
> float field in the database. If I then read that back out in VB and
> cast it to a date (which is automatic if you want) I get back the same
> value.
> However, when I do this in SQL, I get a _slightly_ different date:
> cast(price2 as datetime) as expiry
> returns 2008-06-07 00:00:00.000
> It's off by _two days_. At first I thought this was an epoch issue.
> Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
> Server uses 1/1/1900. Is this understanding correct? If so, how is it
> that the resulting date in SQL is only off by two days, and not 70
> years?
> Maury|||On Apr 29, 3:16=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
> somewhere along the line) that Visual Basic's zero day is 1899/12/30. =A0(=I
> believe it was supposed to be 1899/12/31, so that makes two mistakes, one
> for each day that your calculation is off.)
LOL! Ok, that DOES explain it. I'll just remember to -2 from now on.
Maury

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

convert varchar or char to float

Hello,
Is there a way to convert varchar or char to float?
Thank youSELECT Convert(float, '1e3')-PatP|||Thank you, what is '1e3' and how to apply it my field?|||1e3 is a float constant. I picked it because it can't be a decimal, money, or any other numeric value, either CHAR or FLOAT and that's it.

-PatP|||I have a field in my table "SellTo" datatype set to varchar
value in the field 000021
how can I convert this field to a float(8)

Thank you|||Hello,
I have a query:
Select
cast(convert(char, cast (FielsNameas money(8)),1) as money ) as FieldName

which has to return data in the format like this:.0000
.0000
44.0000
39.0000
.0000
169.0000
.0000
.0000
85.0000
.0000
288.0000
.0000
.0000
.0000
36.0000
36.0000
36.0000
294.0000

What I have is Incorrect:
.0000
.0000
440.0000
390.0000
.0000
.1690.0000
.0000
.0000
850.0000
.0000
.2880.0000
.0000
.0000
.0000
360.0000
360.0000
360.0000
2940.0000

Please help to solv it.

Thank you|||Just curious, but what are you really trying to do? You've posted several questions, all of which "miss the mark" in some important way. I think if you back off just a bit from the technical details and focus on the business task at hand and explain that for us, then we can probably give you much better (both easier to implement/understand and more useful) answers.

-PatP

Convert to float value

Hi guys, the following is the part of my query that I am having an issue.

Select....

.....

,100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

,....

From.....

Where...

That part of the statement should have to give me FLOAT values, such as 60.99 etc. however, it is giving me only the integer part (i.e. 60). I tried to cast/convert the values to float value but I coudn't.

Any idea?

Try changing

,100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

to

,100 * cast(count(c_id) as float)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

|||

Perfect!

my bad, what I did befor is

CAST(100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS FLOAT) AS [Perc]

|||

You can also use a higher precedence data type in the formula.

100.00 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

AMB

|||Cool! that is also working.

Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks

convert question please help

hi experts,
i have coordinates stored in a database in form of 452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 so always take the first number then put the dot then the rest and with the second one take the first 2 numbers put the dot and then the rest, i realized that if i did it with the comma instead of the dot and then convert these 2 in string then it will change the comma to the dot automatically, the result can be also a string.
any help will be very appreciated.

thanksHey mrjoka,

Try this. Hope its what you were lookign for.

Cheers,
Justin

-----------------------
-- Declare variables
declare @.number_1 int
declare @.number_1_len int
declare @.number_2 int
declare @.number_2_len int

-- initialise variables
set @.number_1 = 452031
-- get length of number_1
set @.number_1_len = (select len(@.number_1))
set @.number_2 = 5222500
-- get length of number_2
set @.number_2_len = (select len(@.number_2))

-- Format result
select @.number_1 as old_format, CAST(left(@.number_1, 1) as varchar) + '.' + CAST(right(@.number_1, @.number_1_len-1) as varchar) as new_format
select @.number_2 as old_format, CAST(left(@.number_2, 2) as varchar) + '.' + CAST(right(@.number_2, @.number_1_len-2) as varchar) as new_format|||It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!-- ptp 20071122 see http://www.dbforums.com/showthread.php?t=1624685

DECLARE @.c VARCHAR(50)

SET @.c = '452031 5222500'

SELECT
Stuff(Left(@.c, CharIndex(' ', @.c)), 2, 0, '.')
, LTrim(Stuff(SubString(@.c, PatIndex('% [0-9]%', @.c), 50), 4, 0, '.'))-PatP|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanks|||hi experts,

Oh, stop it!

Regards,|||you stop it

never mind i found it myself with some string manipulation.

thanks for the help|||Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

Regards,|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanksNo, but if you can post the CREATE TABLE statement, and three or more rows of data, then I could probably "wing it" for you.

The general idea would be to write an UPDATE statement that used your source column name instead of the @.c in my example to set the values for the two new columns using the code that I posted. Once you post the schema and a few rows of data, it should only take a minute to cook up and test.

-PatP|||I'm guessing that he means 452031 5222500 are the two values from the columns X and Y, and he wants a "dot" inserted after the first digit of X and after the second digit of Y. I think.
That Stuff function will work like this:

SELECT STUFF(X, 2, 0, '.') AS X, STUFF(Y, 3, 0, '.') AS Y
FROM location

But if you are going to Update that back into columns of type float, you'll probably need a cast.

Is that what you mean mrjoka?|||452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 update daTable
set x = x / 10000
, y = y / 10000|||set x = x / 10000
, y = y / 10000

That's assuming there are always 6 digits in X and 7 digits in Y.|||mrjoka said:

the result can be also a string
and:
i want just column X and Y to be converted

So its kinda unclear whether he wants to update or not|||That's assuming there are always 6 digits in X and 7 digits in Y.no it isn't :) :)|||no it isn't :) :)Eh?

-PatP|||yes!! another canadian!!

i should have said "no it isn't, eh!!"|||A somewhat belated congrats on 10K, BTW!

I must have missed something though... I think that a-dam has a point, in that Mr. Joka wants to shear two digits off the left side, not N digits from the right side. I think that you also need one more zero on your second divisor.

-PatP|||"shear two digits off the left side" would mean that 452031 and 5222500 would become 45.2031 and 52.22500 and he wanted 4.52031

my interpretation was that the decimal should be moved 5 positions to the left, so from 452031 and 5222500 to 4.52031 and 52.22500

"move n positions to the left" is the same as "divide by nth power of ten"

you are absolutely right, i did not count to 5 correctly

the revised query isupdate daTable
set x = x / 100000
, y = y / 100000

my "no it isn't" comment was in response to "That's assuming there are always [x] digits"

and of course "move n positions to the left" or "divide by nth power of ten" works just as correctly on, say, a three-digit number like 937

:)|||Without the schema, we'll never know just what Mr Joka has now... Without knowing what he's got now, we can choreograph the angels in their dance, but they'll still be confined to the head of the pin.

Mr Joka, set the angels free! In other words, please give us the schema and the sample data I requested many posts ago.

-PatP|||in post #1 he said they are float|||in post #1 he said they are float...and displayed examples of them as one unformatted string!

...and described the output as possibly being a string too!

-PatP|||actually he said "i want to change them" and of course there is no output from an UPDATE

and he said "the result can be also a string" which i took to mean it's optional

:)

CONVERT Problem

I am trying to convert values into a scientific notation format so that 0.7130000 would be displayed as 71.3E -01.
I tried "Select convert(float, MeanValue) as Mean from Values" to no avail.
Any ideas?
Karl
What you're looking for is really a string format -- a floating pointnumber can be displayed by the client however it sees fit (i.e.scientific notation or not). Can you do the formatting on theclient side? There is no easy way I know of to format floats inscientific notation in T-SQL.
|||

There are more than one mean and SQL Server doesnot know the mean you are looking for. I will post some samples but if you send me an email I will send you the papers on Descriptive Statistics. In the master look for three System stored procedures sp_frequencies, sp_median and sp_means run a search for them in the BOL(books online). I would also browse the T-SQL books by Ken Henderson at my local book store. Hope this helps.

SELECT harmonic_mean=
COUNT(*)/SUM(1.0/N_Of_Employees)
FROM dbo.CustomerSummary

harmonic_mean
-------
4.4220114749932304

SELECT geometric_mean=
POWER(10.0000, SUM(LOG10(N_Of_Employees*1.0))/COUNT(*))
FROM dbo.CustomerSummary

geometric_mean
-----
4.8608

|||Adam,
Thanks. I did a search and I think I am getting closer. I just tried:
Dim MyDouble As Double = 5700000000000
lblMsg.Text = MyDouble.ToString("E")
And I got it so I am going to play with it some more.
Karl

Convert int to float

Hi,
why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lotWhat method are you using to convert?
How long is so long?

Your last sentence doesn't make a lot of sense - if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

In fact, please post the SQL statement in question as well :)|||First of all, converting anything to float is a "computationally noisy" operation... It means a lot of work for the CPU no matter how you do it, just because of how the FLOAT datatype is stored.

My preference for doing what you've described is peculiar, but it works well for me... I would try using:SELECT 1e0 * inumber1 / inmuber2-PatP|||Thanks a lot Pat, that's a nice trick!|||What method are you using to convert?
How long is so long?

It was Alter Table A Alter Column C float NOT NULL and took about 20 Minutes.

if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

That's why I did the Alter Command. I just wondered why its such a big thing from int to float|||for very large tables, it probably would be faster to export the table to a flat file using bcp, then reimport to a different table with the new datatype, again with bcp.

I tend to shy away from ALTER TABLE.|||As I see it, there is three main reasons for this operation to take take quite a bit of time:

First, creating the float value requires processing power. However, this does not explain how updating a table with 5M records takes so long.

Second, A float takes 8 bytes, whereas int takes 4 bytes. So, depending on the width of the table, you may experience page splits (There is no longer room for all the rows in the page, so some rows have to be moved to a new page). I'm pretty sure this will be especially bad if there is a clustered index on the table.

And last, if this is done in an active environment, keep in mind that you'll require exclusive schema lock on the table, so your command will have to wait until a point in time when there is no activity against the table.

So, finally... I don't think queries will run any faster. You'll probably end up having more data pages, and I'm pretty sure that the negative impact from this is of greater magnitude than the cast from int to float.|||Wow, that was a really detailed comment :-) thank you

As I already have converted, does it make any sense to reconvert to integer regarding the negative impact of greater magnitude?|||int to float

What the hell for?

There's no precision in int. Are you looking for precision? Then use decimal.

Let me ask...does anyone use float or real, and if so what for?

I gotta do a poll|||Have you ever divided two integer values?|||int to float

What the hell for?

There's no precision in int. Are you looking for precision? Then use decimal.

Let me ask...does anyone use float or real, and if so what for?

I gotta do a pollI think you mean "There's no precision in float". And a float is more precise when you do complex math (the sort of complex I don't really understand frankly). Michael Valentine Jones did a good proof on SQLTeam. I'm sure Pat could explain too. I've got a link to a page that goes into this in depth but have never had chance to read it yet.
EDIT - linky: http://docs.sun.com/source/806-3568/ncg_goldberg.html

Anyway - you are right though - for most purpses you would prefer decimal over floats.|||Have you ever divided two integer values?You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?|||Correct me, if I'm wrong, but float takes less space than decimal, doesn't it?|||You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?
Not really, but 5M rows is still pretty small. Frankly, I thought one of you could give me that answer ;-)|||BOL

float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Decimal

Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

float [ ( n ) ]

Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.

So How many bits in a byte...I think it's 8...but bol doesn't say what the default for float alone is

Looks like in EM in table design that float defaults to 8 bytes, so that would be half of what int is|||Queries should be a bit faster after that..

Ok, so this is you stated goal

supply us with DDL and the query that's "slow"

data type conversions are not where I would start for Perf Tuning|||exsqueeze me - shows my naivite of the subject. I think rather than floating point data types I should have said floating point calculations.

But in any event, if you want an accurate representation of the division of two integers a float is probably the wrong route.|||excuse me if I am wrong but I think one of the major drawbacks of floats is that you are not always guaranteed reproduceable results. I might be wrong about this.|||Ok - I found the link.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391
It is really a display issue when you use a float. The problem is that float is internally a binary representation of the number, and the binary representation cannot always be converted directly to a decimal representation that is exactly equivalent. Float is intended for scientific applications where computational accuracy is the most important requirement.
My point was that Decimal is not always better (read more accurate) than Float. I probably should have made the point better & more succinctly though since in this case it is certainly better to use decimal.|||that's a great quote, pootle, thanks for following that up

better to use decimal? that depends on what kind of data you're storing

if it's money, yes :)

and pat will tell you it had better be DECIMAL(n,4), not 2 -- and then rounded by the front end if desired|||Have you ever divided two integer values?yup, and if i don't want a whole number answer, i use pat's trick, although mine is just slightly different:select 1.0 * col1 / col2:)

convert float to string

How can I convert a float value to a string, without rounding issues or
ending up with scientific notation ?
Seems like a simple request. I have various types of numbers stored in a
Float field that I need to convert to a string as is. I don't want
rounding, nor do I want additional decimal places. I want the number as is.
And I can't use a different data type...I'm stuck using Float.
CAST( 1 to 999999) works fine.
CAST( > 999999) returns scientific notation. There's one I wasn't
expecting.
I don't necessarily know how many decimal places the number has, so if I use
STR(), I don't know how many decimal places to tell it. If I don't specify
number of decimal places, it rounds to nearest Integer. Not good.
I want (float) 12345678.90 to = (string) 12345678.90
Any ideas ? I've wracked my brain and others up till this point.
Thanks a bunch !
BillHave you read through this, yet?
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML
http://milambda.blogspot.com/|||Hi Bill,
I think you'll have to use str to convert it to a varchar - and then
manipulate that varchar as you require. Something like this...
--function
drop function dbo.fnFloatToString
go
create function dbo.fnFloatToString(@.x float) returns varchar(50) as
begin
declare @.s varchar(50)
select @.s = rtrim(ltrim(reverse(substring(z, patindex('%[^.]%', z),
50)))) from
(select substring(y, patindex('%[^0]%', y), 50) as z from
(select reverse(str(@.x, 40, 20)) as y) a) b
return @.s
end
go
--data
declare @.t table (x float)
insert @.t
select 1
union all select 123
union all select 12345678
union all select 123456789012345
union all select 1.2
union all select 1.234567
union all select 1.2345678901234
--results
select x, dbo.fnFloatToString(x) from @.t
"ML" wrote:

> Have you read through this, yet?
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
>
> ML
> --
> http://milambda.blogspot.com/

Convert Float to Decimal errors

I'm trying to move records from a SQL table with a float column to a DB2 database that has the column defined as Decimal (8,2) It keeps crashing saying it has a type mismatch problem. I tried changing my source command to pass in the column already converted and it still crashes on this. I also tried doing a data conversion task to do the conversion and I still get the same error. Any ideas?

Hi,

What you can do is, convert the field to 'varchar' type while reading from sql server and then change it to 'Decimal' while loading into DB2.

|||I'll give that a try. Thanks

Convert float to char

Select Cast('100.1234' as float)
give me the result 100.1234
Now when I convert it back to char I want exactly 100.1234
Select Convert(char(100),Cast('100.1234' as float))
Gives me 100.123 (Here I was expecting 100.1234)

When I do
Select STR(Cast('100.1234' as float),25,4)
I get back the result as 100.1234

However here I am not sure how many digits do I have after the decimal
point. If I put some value like
Select STR(Cast('100.1234' as float),25,8)
I get 0's appended to it, which is again not desired.

Thanks in advance,
Jai
jaijai_kumar@.hotmail.com wrote:

> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

Hi Jai. I want to warn you about going down this path.
You will never get exactly what you want all the time,
because there is a base converson from base 10 to binary,
and there are lots of distressingly simple-seeming
decimal numbers that do not convert exactly to a binary
float. Then converting back will show the loss by giving
a different value. I suggest using a DECIMAL datatype
if you want to retain exactitude...

Joe Weinstein at BEA Systems|||(jaijai_kumar@.hotmail.com) writes:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.

Since a float is an approximate number, this is not any exact science.
Consider:

SELECT cast('100.1235' as float)

this gives in Query Analyzer:

100.12350000000001

So when you convert it to string, which value do you want?

Anyway, here is a horrible expression that achieves what you are looking
for. But note the caveate above, and be aware that you may not always
get what you want.

Select reverse(substring(x, patindex('%[^0]%', x), 25))
from (Select x = reverse(ltrim(str(Cast('100.1235' as float), 25, 8)))) y

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is another horrible expression, that does the same thing:

select replace(rtrim(replace(ltrim(str(Cast('100.1235' as float),
25, 8)),'0',' ')),' ','0')

Razvan|||Hello all,

Thanks a lot for all your messages.
Erland your work around gave me a good hint.
I have created a small function to do the same.

Jai
jaijai_kumar@.hotmail.com wrote:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

CONVERT Float to char

We have a table that was imported from Access to SQL 2000. For some reason
the old data had phone numbers set as Float. ? If anyone can explain that
I'd love to hear it.
Anyway, I have a SQL 2000 table that has a 10 character column called
Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
just get the 10 character phone converted from Float to char?
Thank you,
AnthonyUse the STR Function STR(F, N, R) ,
Where F is any numeric value,
N is integer total number of characters you want output, and
R is integer Number of characters to right of decimal point
as example
Declare @.F Float Set @.F = 8052080080
Select @.F, Str(@.F, 10, 0)
"Anthony W DiGrigoli" wrote:

> We have a table that was imported from Access to SQL 2000. For some reason
> the old data had phone numbers set as Float. ? If anyone can explain tha
t
> I'd love to hear it.
> Anyway, I have a SQL 2000 table that has a 10 character column called
> Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
> just get the 10 character phone converted from Float to char?
> Thank you,
> Anthony

Convert field

I am having a bit of a problem. I have a date field that is comming from
a different platform as a float. I need to convert these records to
datetime or small datetime. Is their a Stored Procedure to do this in SQL
or what is the quickest way to do this?
Thanks>> I have a date field [sic] that is comming from a different platform as a floa
t. <<
You don't know that a column is not anything like a field!
You don't know that a row is not anything like a record!
You might be able to do a CAST(foobar AS DATETIME). the quickest way
is to keep temporal data in temproal columns.|||You need to find out the starting point, for inhstance, what does 0.5
mean? Noon of what day?
Suppose 0 means midningt of January 1st, 2000.
Use dateadd function to add days (the integer part of the float) to
that start date
Then use dateadd to add seconds|||I have to suggestions.
1. The code which is performing the transformation from the other platform
may be able to convert as it goes.
2. You can Cast the float to a datetime list this...
Declare @.Mydate Float
Set @.Mydate = Cast( 1.125 as Float )
Select Cast( @.Mydate as DateTime)
However, it will require that the float from the other platform is using 0
as the same Epoch of 1900-01-01 00:00:00.000 .
Regards
Colin Dawson
www.cjdawson.com
"JimS" <noholycowsspam@.ya_NoJunk_hoo.com> wrote in message
news:O1Al2EOXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I am having a bit of a problem. I have a date field that is comming from
> a different platform as a float. I need to convert these records to
> datetime or small datetime. Is their a Stored Procedure to do this in SQL
> or what is the quickest way to do this?
> Thanks
>