Formula in flaot field

I have a float field and need the data to present itself as a datetime. I have been unsuccessful at converting the entire field to datetime so I thought I would try a formula but have had no success. Can this be done?

I have data like so:

100599
10699
120597
etc...

and need to hit them as though they were datetime for order by and such...

or an 'in query' conversion...

ThanksI came up with a solution, thanks anyway.sql

Formatting in sql query

Hello All,

Is it possible to format an integer to a float value with 2 decimals in an sql query.

If yes, Please help?

SELECT

CONVERT(DECIMAL(4,2),9)

You can replace number 9 above with your integer or integer column.

|||

Thanks Limno,

You are a life saver.

Formatting a float variable to 2 decimal places

Hey,

I am filling a temp table with various float variables and I need to format one particular column to 2 decimal places.

Does anyone know the correct syntax to do this, and should it be done before filling the temp table or when I select what I needs from the temp table?

ThanksThis should be done at the application level.|||Ok, I'll look into that|||If at all possible, use decimal (x,2) instead of float. Float is considered an approximate datatype and if you have lots of math/rounding it could introduce errors in your application.

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
See other post..
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @.testFloat float
select @.testFloat = 1000000.12

select convert(varchar(100),@.testFloat) -- gives 1e+006
select cast(@.testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@.testFloat as money)) -- gives
1000000.12

select @.testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@.testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
DaveTry specifying the desired precision and scale on your decimal/numeric
declaration:

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||Dan and Igor, both examples worked great, thanks very much.

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))
SELECT STR(@.testFloat, 38, 2)

Cheers
Dave

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:

> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Format a float number

Hi
I am new to reporting servcies. I am wondering about the syntax I can use
to format a field in the report. The field is summing up a float column, and
I want only two decimals after the comma to be displayed.
This is the Value: =Sum(Fields!Samtals.Value)
What can I put in the format propertie for this field so a number like
123,4325345 becomes 123,42?Textboxes have a FormatCode property which allows you to specify a .NET
formatcode. For you particular example you would use N2 which gives you 2
decimals.
More details on formatcodes can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ágúst Valgarð" <gstValgar@.discussions.microsoft.com> wrote in message
news:FCCBD2DE-858A-41E0-924D-282854ED6BC7@.microsoft.com...
> Hi
> I am new to reporting servcies. I am wondering about the syntax I can use
> to format a field in the report. The field is summing up a float column,
and
> I want only two decimals after the comma to be displayed.
> This is the Value: =Sum(Fields!Samtals.Value)
> What can I put in the format propertie for this field so a number like
> 123,4325345 becomes 123,42?
>