Tsql Datediff to Return Number of Days with 2 Decimal Places

TSQL DateDiff to return number of days with 2 decimal places

Take the DateDiff in seconds instead, and then divide by 86400.0. The decimal point is required.

DATEDIFF() to just return age with 2 decimal points

You can cast to a decimal with the precision you want:

SELECT CAST(DATEDIFF(day, BIRTH_DATE, TEST_DATE)/365.25 as DECIMAL(10, 2)) as [Age at Result]
FROM TABLE
WHERE ID = 100;

Note: I removed the single quotes around "100". Only use single quotes if it the id is a string.

Display decimal points for DateDiff function in SQL

Your code works. You can run this DB Fiddle or this code:

DECLARE @StartDate datetime = '2017-12-14 15:13:00.000';
DECLARE @EndDate datetime = '2017-12-21 00:00:00.000'
DECLARE @temp DECIMAL(10,2) = CAST(DATEDIFF(second, @StartDate, @ENDDate) as Decimal(10,2))/86400.0
DECLARE @temp2 DECIMAL(10,2) = DATEDIFF(second, @StartDate, @ENDDate)/86400.0

select @temp, @temp2;

So, what simple mistake would cause you to get the results that you are seeing? There is one: if you have defined your "date" variables as date rather than datetime.

I suspect that you have:

DECLARE @StartDate date = '2017-12-14 15:13:00.000';
DECLARE @EndDate date = '2017-12-21 00:00:00.000'

How to calculate difference in hours (decimal) between two dates in SQL Server?

DATEDIFF(hour, start_date, end_date) will give you the number of hour boundaries crossed between start_date and end_date.

If you need the number of fractional hours, you can use DATEDIFF at a higher resolution and divide the result:

DATEDIFF(second, start_date, end_date) / 3600.0

The documentation for DATEDIFF is available on MSDN:

http://msdn.microsoft.com/en-us/library/ms189794%28SQL.105%29.aspx

datediff() of 2 timestamps that contain decimal values

Please note that DATEDIFF(MS, ...) requires guarding for long time spans or it will give an overflow:

SELECT datediff(MS, '2013-06-30 23:59:59.997', '2013-06-01 21:59:59.141')
-- FAILURE: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large

You can use following method which is overflow-safe and gives you a float result:

SELECT cast(cast('2013-06-01 23:59:59.997' as datetime)-cast('2013-06-01 21:59:59.141' as datetime) as float) * 24.0
-- Returned 2.00023796296296

SELECT cast(cast('2013-06-30 23:59:59.997' as datetime)-cast('2013-06-01 21:59:59.141' as datetime) as float) * 24.0
-- Returned 698.000237962963

I'm not sure how this method works when time zone changed during the measured date period.

SQL return a decimal when dividing a INT by DATEDIFF

I just multiply by 1.0:

Units.Units * 1.0 / DATEDIFF(week, StartofSales.[Start of Sales/Letting Revised],EndofSales.[End of Sales/Letting Revised]) 

This converts the value to a numeric so the division has decimal places.

SQL Datediff in seconds with decimal places

The StackOverflow magic seems to have worked, despite spending hours on this problem last week, I re-read my question and have now solved this. I thought I'd update with the answer to help anyone else who has this problem.

The problem here was not that there was a large range, there was a negative range. Which obviously results in a negative overflow. It would have been helpful if the SQL Server error was a little more descriptive but it's not technically wrong.

So in my case, this was returning values:

SELECT * FROM pagelog
WHERE pagelog_created > pagelog_end

Either remove the values, or omit them from the initial result set!

Thanks to Ivan G and Andriy M for your responses too

---

Related Topics