October 29, 2010 at 3:09 am
Hi,
I am trying to convert DateTime type to Double in a query. I want convert 07:30.00 to return 7,5
Select Datepart(hh;timer)+(Datepart(mi;timer)/60) as Expr1
From tblTimeRecorder;
This sort of works, but the datatype that Sql server suggest returns no whole numbers.
Can anybody help me?
October 29, 2010 at 2:40 pm
CONVERT(float, [DatetimeValue])
_____________
Code for TallyGenerator
October 29, 2010 at 3:29 pm
It's easy, but complicated 😉
SQL Server will evaluate each expression within your equation and will determine the returned data type. Based on that it will define each subresult as well as the final result based on the precedence of each data type involved.
Example 1:
SELECT DATEPART(hh,GETDATE())+(DATEPART(mi,GETDATE())/60)
will be evaluated as
SELECT INTEGER + (INTEGER/INTEGER)
Now the "tricky" part: INTEGER/INTEGER will return a INTEGER data type as well (even if "we know", that's not always correct...).
So, instead of 1/2 = 0.5 it'll return 0.
To get the result as expected we need to "adjust" the data types involved to get the desired result:
Example 2:
SELECT DATEPART(hh,GETDATE())+(DATEPART(mi,GETDATE())/60.0)
will be evaluated as
SELECT INTEGER + (INTEGER/DECIMAL). This will be evaluated as INTEGER + DECIMAL since DECIMAL has a higher precedence than INTEGER. (see BOL, the SQL Server help system, for details). And, based on that very same logic, your final result will be DECIMAL instead of INTEGER.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply