Explicit conversion from data type datetime2 to float is not allowed.

by

Recently, I was working on a requirement that involved calculating the time difference. Still, after executing the SQL query, I encountered the error “Explicit conversion from data type datetime2 to float is not allowed.”. Let’s delve into understanding why this happens and explore the best ways to address it.

Explicit conversion from data type datetime2 to float is not allowed.

Below is the SQL query I executed.

SELECT CAST(EndTime AS float) - CAST(StartTime AS float) AS DaysDifference
FROM RunDetails
WHERE ID = 1;

I encountered this error immediately after executing the query above, as shown in the screenshot below.

Explicit conversion from data type datetime2 to float is not allowed.

Why This Error?

SQL Server has strict rules about data type conversions. When you try to directly convert a datetime2 value to a float using CAST or CONVERT functions, SQL Server refuses this operation with this error.

This happens because datetime2 and float data types are not directly compatible for conversion in SQL Server. Unlike the older datetime type, which had some conversion tricks, datetime2 (introduced in SQL Server 2008) requires specific approaches to be converted to numeric formats.

Solution

The most reliable approach is to use a two-step conversion process:

-- Step 1: Convert datetime2 to datetime
-- Step 2: Convert datetime to float

SELECT CAST(CAST(EndTime AS datetime) as float) - CAST(CAST(StartTime AS datetime) as float) AS DaysDifference
FROM RunDetails
WHERE ID = 1;

After executing the above query, I got the expected output successfully without any error as shown in the screenshot below.

Explicit conversion from data type datetime2 to float is not allowed

Best Practices

It is recommended:

  • Avoid unnecessary conversions – Store data in its natural type when possible
  • Use computed columns for frequent conversions:
ALTER TABLE MyTable ADD DateAsFloat AS CAST(CAST(MyDatetimeColumn AS datetime) AS float) PERSISTED;
  • Document any conversions with clear comments explaining the logic
  • Consider timezone impacts when converting datetime2 values

Conclusion

While SQL Server doesn’t allow direct conversion from datetime2 to float, several effective workarounds exist to achieve this requirement. The best approach depends on your specific needs regarding precision, performance, and compatibility.

The most reliable method for most scenarios is the two-step conversion through the datetime type.

You may also like following the articles below.