SQL Kiwi (5/5/2012)

---

The explanation could be better. The question does not involve a float value. By default, the literal value 0.25 is interpreted as a numeric(2,2):

SELECT

0.25 AS col1

INTO #v;

EXECUTE

tempdb.sys.sp_columns

@table_name = N'#v',

@table_owner = N'banana',

@table_qualifier = N'tempdb',

@column_name = N'col1';

DROP TABLE #v;

If the intention has been to show a float, either a float literal or a typed variable could have been used:

SELECT CAST(25e-2 AS datetime);

DECLARE @f float = 25e-2;

SELECT CAST(@f AS datetime);

Datetime values are no more "associated" with a float value than they are with any other type that can be implicitly converted (see the conversion table in Books Online - CAST and CONVERT (Transact-SQL)). The internal representation is two integers - one for the number of days from the base date, and one for the number of ticks (1/300th second) since midnight. In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

DECLARE @dt datetime = '1900-01-02 00:00:00.006';

SELECT @dt;

SELECT CONVERT(binary(8), @dt);

My biggest concern with this question though, is that it encourages people to be sloppy with types and relies on hidden implicit conversions. As a general rule, try to be explicit about types in T-SQL code.