I need to compare 2 dates and return the number of days in between with 2 decimal places.
For example:
when comparing
SubmittedDate = 2012-02-29 07:02:55.000 FirstCall = 2012-02-29 12:12:19.000
That is a 5 hour difference. So I would return 0.2 days
I have tried:
CAST(DATEDIFF(Hour, SubmittedDate, FirstCall)/30.0 AS DECIMAL(5,2)) As HoursBeforeFirstCall CAST(DATEDIFF(Day, SubmittedDate, FirstCall) AS DECIMAL(5,2)) As HoursBeforeFirstCall
None seem to work.
In this case: datediff returns an int, an the literal 60 also represents an int. So, just like others mentioned, changing the 60 to a numeric type by adding . 0 to it makes the output a numeric type, which includes the decimal places.
SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
Take the DateDiff
in seconds instead, and then divide by 86400.0
. The decimal point is required.
When you represent a date as a number, each day is represented by 1.0 "units" already. To get the timespan of two dates as a decimal, you can just subtract them.
SELECT CAST((@FirstCall - @SubmittedDate) AS NUMERIC(10, 2))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With