SQL Full day difference

Question

Does anyone know how to get the datdiff in full days between 2 days.

Im currentlly using

datediff(day,createddate,dateserved) 

But need it to return how many full days

i.e

Created    =   1/7/2010 2100
dateserved =   2/7/2010 2000

currently the datediff would show 1 day but i need it to show 0 until dateserved passes 2100

Any ideas Sp

Answer 1

You could use:

DATEDIFF(dy, created_date, date_served) -
CASE
    WHEN CAST(created_date AS TIME) > CAST(date_served AS TIME) THEN 1
    ELSE 0
END

I originally proposed trying to use division, but when you get down to milliseconds you can quickly hit arithmetic overflows.

Answer 2

What you can do is go with the smallest possible resolution in the DATEDIFF() function you can feasibly get away with (minutes,seconds, whatever). Then to math to convert that to a day representations.

I'm basically proposing:

Floor(DATEDIFF(mi, createddate, dateserved)/60/24);

Answer 3

 SELECT FLOOR(CAST(dateserved AS FLOAT) - CAST( createddate AS FLOAT))

Also the following seems to work and be more concise but may need some testing

SELECT FLOOR(CAST(dateserved-createddate AS FLOAT))

Answer 4

@Ian Jacobs got it in first, but here's how I'd do it in T-SQL. Assuming you're only concerned with hours:

DECLARE
  @From  datetime
 ,@Thru  datetime

SET @From = 'Jan 1, 2010 21:00'
SET @Thru = 'Jan 3, 2010 20:00' -- 2/7/2010 2000

print datediff(dd, @From, @Thru)
print datediff(hh, @From, @Thru)
PRINT datediff(hh, @From, @Thru) / 24

...that is, calculate the hours difference between your datetimes, divide by 24, and truncate the decimal value. SQL appears to truncate, but if you're paranoid, use

print datediff(hh, @From, @Thru) / 24.0
PRINT floor(datediff(hh, @From, @Thru) / 24.0)

to ensure proper truncation. If you need precision down to the minute, second, or millisecond, add bit more arithmatic.