FLOAT datatype weirdness

Hello - I have the following two sets of very simple SQL
--Example #1
DECLARE @.BaseNum float
SELECT @.BaseNum = RAND(
(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
print @.BaseNum
go
--Example #2
SELECT RAND(
(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
go
WHY does example #1 only return approx 4, 5, or 6 digits to the right of the
decimal, while Example #2 returns 17 digits to the right of the decimal. I
have tried everything including CAST as CHAR(20), CONVERT(char(20),@.Basenum)
,
etc.
Can someone please provide some input?
ThanksThe results are different because PRINT implictly casts its output to a
string. Exactly what result do you want? FLOAT is an inexact numeric so if
you want to control the decimal precision you will need to cast it as
NUMERIC for example. On the other hand if you just want to *display* a fixed
number of decimal places then just let the application handle that
formatting client-side.
David Portas
SQL Server MVP
--|||MSSQLServerDeveloper wrote:
> Hello - I have the following two sets of very simple SQL
> --Example #1
> DECLARE @.BaseNum float
> SELECT @.BaseNum = RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> print @.BaseNum
> go
> --Example #2
> SELECT RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> go
> WHY does example #1 only return approx 4, 5, or 6 digits to the right
> of the decimal, while Example #2 returns 17 digits to the right of
> the decimal. I have tried everything including CAST as CHAR(20),
> CONVERT(char(20),@.Basenum), etc.
> Can someone please provide some input?
> Thanks
I don't see a difference here on those queries you supplied in SQL 2000
SP3a. Float is an inexact data type, so that might have something to do
with it.
What version of SQL Server (and SP) are you using.
David Gugick
Imceda Software
www.imceda.com|||I'm using 2k with sp2.
I solved it defining the variable @.BaseNum decimal(18,17)
"David Gugick" wrote:

> MSSQLServerDeveloper wrote:
> I don't see a difference here on those queries you supplied in SQL 2000
> SP3a. Float is an inexact data type, so that might have something to do
> with it.
> What version of SQL Server (and SP) are you using.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||MSSQLServerDeveloper wrote:
> I'm using 2k with sp2.
> I solved it defining the variable @.BaseNum decimal(18,17)
Why are you only on SP2 and not on SP3a?
David Gugick
Imceda Software
www.imceda.com|||The default conversion from float to string yields only
6 digits of precision, and by using PRINT to display the
result you are using this conversion (as David pointed
out). In the second example, you are using SELECT,
which returns the float to the client for display without
first converting it to a string. Most clients will display
15 or 16 digits of precision for a float.
The actual float value contains the full precision in
each case, but you are only seeing part of it when
you use PRINT.
Be aware that RAND(), when used with a parameter,
is not random, but is completely reproducible and
deterministic. See
http://groups.google.co.uk/groups?q=0.7135733602414355
for more on this.
Steve Kass
Drew University
"MSSQLServerDeveloper" wrote:

> Hello - I have the following two sets of very simple SQL
> --Example #1
> DECLARE @.BaseNum float
> SELECT @.BaseNum = RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> print @.BaseNum
> go
> --Example #2
> SELECT RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> go
> WHY does example #1 only return approx 4, 5, or 6 digits to the right of t
he
> decimal, while Example #2 returns 17 digits to the right of the decimal.
I
> have tried everything including CAST as CHAR(20), CONVERT(char(20),@.Basenu
m),
> etc.
> Can someone please provide some input?
> Thanks
>

FirstWeekOfYear as parameter of DatePart

Haw to make FirstWeekOfDay parameter work in SQL Server 2000.
In Access 2000 datepart function has 3 parameters.
Why it is't in SQL Server 2000.
Help pleaseStraight from Books Online:

SET DATEFIRST
Sets the first day of the week to a number from 1 through 7.

Syntax
SET DATEFIRST { number | @.number_var }

Arguments
number | @.number_var

Is an integer indicating the first day of the week, and can be one of these values.

Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday

Remarks
Use the @.@.DATEFIRST function to check the current setting of SET DATEFIRST.

The setting of SET DATEFIRST is set at execute or run time and not at parse time.

Permissions
SET DATEFIRST permissions default to all users.

Examples
This example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
GO