converting datetime to float in sql
Hello, I have a question, how do I convert the difference of the following dates, for example: (2023-01-02 18:00:00)-(2023-01-01 17:00:00) into a decimal number?
I know how the conversion to an integer happens(cast(cast(test as date) - cast(testtwo as date) as int), but here I am confused.
You man look at DATEDIFF function. It allows you to:
returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
Then, convert the seconds to a day:
SELECT DATEDIFF(SECOND, '2023-01-01 17:00:00', '2023-01-02 18:00:00') * 1.0 / (60*60*24) -- 1.041666666666
1.0 rather than 1 is used here. Without using a decimal constant the calculation would be performed using integer arithmetic and the result would be 1. The rules for data type precedence will cause the other integer values to be converted to decimal. DATEDIFF( ... ) / ( 60.0 * 60 * 24 ) would work just as well.The DATETIME datatype is incredibly useful and flexible for things like this, although the rounded 3.3 millisecond resolution causes some folks pain.
If you want "Decimal Days" where the fractional part to the right of the decimal point represents time as a partial day, then the following will do the trick.
--===== Setup test values for clarity and ease of testing.
-- These variables could be changed out for columns in a table.
DECLARE @StartDT DATETIME = '2023-01-01 17:00:00'
,@EndDT DATETIME = '2023-01-02 18:00:00'
;
--===== Calulate the difference (duration) in decimal days.
SELECT DecimalDays = CONVERT(FLOAT,@EndDT-@StartDT)
;
